Doctrine Many To Many Inner Join

Question

I'm trying to get manyToMany relationship with Doctrine and symfony. I'm new at Doctrine and Symfony. I came from Zend framework.

I've created the 3 tables: Post, PostCategory and junction table PostToCategory as you can see below.

My goal is to do inner join and to get for every post its categories. This is what I've done so far:

//CMS/GBundle/Entity/PostContent

class PostContent
{

    /**
     * @ORM\ManyToMany(targetEntity="CMS\GBundle\Entity\PostCategory", inversedBy="posts")
     * @ORM\JoinTable(name="post_to_category")
     */
    protected $categories;

    public function __construct()
    {
        $this->categories = new ArrayCollection();
    }

//CMS/GBundle/Entity/PostCategory

class PostCategory
{

    /**
     * @ORM\ManyToMany(targetEntity="CMS\GBundle\Entity\PostContent", mappedBy="categories")
     */
    protected $posts;

    public function __construct()
    {
        $this->posts = new ArrayCollection();
    }

I would like now to create function that returns me joined data Post->PostCategories.

Where should I create function ? in PostToCategory Repository ? or somewhere else ? How should my query look like ?

I've tried a lot of options and I passed all the possible questions on Stack but I could not get it done..

Thanks in advance!

Update:

This is what i get when i do findAll method on PostContent repository.

Answer 1

You can create repository class for your entity, for example:

CMS/GBundle/Repository/PostContentRepository.php

<?php

namespace CMS\GBundle\Repository;

use Doctrine\ORM\EntityRepository;

class PostContentRepository extends EntityRepository
{
    public function getPostsWithCategories()
    {
        $qb = $this->createQueryBuilder('post_content')
            ->select(['post_content', 'post_categories'])
            ->join('post_content.categories', 'post_categories')
        ;

        return $qb->getQuery()->getResult();
    }
}

CMS/GBundle/Entity/PostContent.php

<?php

namespace CMS\GBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Table()
 * @ORM\Entity(repositoryClass="CMS\GBundle\Repository\PostContentRepository")
 */
class PostContent
{
    /* Your entity properties and methods */
}

And then you can use it in anywhere. For example, in the controller:

<?php

namespace CMS\GBundle\Controller;

use ;

class SomeController extends Controller
{
    public function someAction()
    {
        $posts = $this->getDoctrine()
            ->getManager()
            ->getRepository('CMSGBundle:PostContent')
            ->getPostsWithCategories()
        ;

        /* Your logic */
    }
}

Answer 2

The preferred way to make a relationship is to create your entities like you did, then run a console command to extend your entity with some getters and setters:

$ bin/console doctrine:generate:entities AppBundle

and then let doctrine create your tables:

$ bin/console doctrine:schema:update --force

Now that is everything ready you have to fill some data in your database tables.

$category = new Category();
$categroy->setName('PHP Programming');
$em->persist($category);

$post = new Post();
$post->setTitle('My first Blogpost');
$post->addCategory($category);
$em->persist($post);

$em->flush();

After that can get it out. I just give you an example

public function indexAction($id)
{
    $em = $this->getDoctrine()->getManager();
    $category= $em->getRepository('AppBundle:PostCategory')->find($id);

    // test
    foreach($category->getPosts() as $post)
    {
        echo $post->getTitle() . '<br>';
    }
}

To load all the posts immediately instead of lazy loading i suggest to write your own query in the CategoryRepository class and change the find() method-name for your own method-name.

Answer 3

As Frankbeen advised me i created my custom query:

public function getPostCategories() {

    $data = $this->createQueryBuilder('c')
        ->select('c.id, pc.name')
        ->innerJoin('CMSGBundle:PostToCategory', 'ptc', 'WITH', 'ptc.postId = c.id')
        ->innerJoin('CMSGBundle:PostCategory', 'pc', 'WITH', 'ptc.categoryId = pc.id')
        ->getQuery()
        ->getArrayResult();

    return $data;

}

In Method above I'm fetching just post.id that is related to post_to_category_post_id category name

In controller I generate two queries one to fetch all Posts second to fetch data using getPostCategories method.

Controller:

$em = $this->getDoctrine()->getManager();

    $posts = $em->getRepository('CMSGBundle:PostContent')->findAll();
    $postCategories = $em->getRepository('CMSGBundle:PostContent')->getPostCategories();

View:

{% for post in posts %}
   {% for category in categories %}
      {% if category.id == post.id %}

         {{ category.name }}       

      {% endif %}
   {% endfor %}
{% endfor %}

I know that this is not the best solution and please if anyone can suggest me with minifing same code or how to write it better I will be grateful!