Query on a Many-To-Many Relationship Using Doctrine with Symfony2

Query on a many-to-many relationship using Doctrine with Symfony2

You can write a join DQL query as below

$em = $this->getContainer()->get('doctrine')->getManager();
$repository = $em->getRepository('YourNamespaceYourBundle:User');
$query = $repository->createQueryBuilder('u')
    ->innerJoin('u.groups', 'g')
    ->where('g.id = :group_id')
    ->setParameter('group_id', 5)
    ->getQuery()->getResult();

Your mapping for groups property in User entity will handle join part itself you don't have to mention the junction table in your DQL query

Many-to-many in QueryBuilder

1. Use many-to-many bidirectional relationship

Always use ->from('App:Animal', 'animal')->join('animal.group', group) for table which contains data you want. Don't do the opposite ->from('App:Group', 'group')->join('group.animals', 'groupAnimal'). For SQL you get same results but Doctrine can't handle it as you want.

Use inner join to retrieve only entities that are assigned to any group:

$entityRepository->createQueryBuilder()
    ->select('animal')
    ->from('App:Animal', 'animal')
    ->innerJoin('animal.group', group)

More about bidirectional relationship: https://www.doctrine-project.org/projects/doctrine-orm/en/2.8/reference/association-mapping.html#many-to-many-bidirectional

2. Unidirectional one-to-many relationship (recommended)

In this scenario you should break many-to-many relationship and create new connection table App:AnimalGroup which connect these two tables together. It is not convenient for all use-cases but it is more future proof, more simple to read and prepared for adding metadata for relation:

use Doctrine\ORM\Query\Expr\Join;

$entityRepository->createQueryBuilder()
    ->select('animal')
    ->from('App:Animal', 'animal')
    ->innerJoin('App:AnimalGroup', 'animal_group', Join::WITH, 'animal_group.animal = animal')

In this case is up to you if you want unidirectional or bidirectional relationship. Bidirectional relationship is recommended only if child side didn't contains too many entries.

Many To Many relationship in Doctrine

You can do this with createQueryBuilder
On your controller, you can add this :

$qb = $entity_manager->createQueryBuilder();
$qb
    ->select('movie')
    ->from(Movie::class, 'movie')
    ->leftJoin('movie.actors', 'actors')
    ->addSelect('actors')
;

$movies = $qb->getQuery()->getResult();

And pass $movies on your template.

The addSelect method on the query allows doctrine to identify that is a many to many relationship between Actor and Movie.

On your template, you can show the result like this :

<ul>
    {% for movie in movies %}
        <li>{{ movie.title }}</li>
        <ol>
            {% for actor in movie.actors %}
                <li>{{ actor.name }}</li>
            {% endfor %}
        </ol>
    {% endfor %}
</ul>

Symfony2 and Doctrine: Many-to-many relation using query builder

>innerJoin('a.users', 'cu', 'WITH', 'cu = :collectionUser')

or leftJoin should do the trick

Symfony join query with One-To-Many relation

That was WAY much less complicated than I have imagined... I didn't have to create a new query, all I had to do was to import a TermAssign repository to show() function located in OfferController.php and use findBy() function to fetch terms from Term table. Solution below:

OfferController.php:

/**
     * @Route("/{id}", name="offer_show", methods={"GET"})
     */
    public function show(Offer $offer, int $id, TermAssign $terms): Response
    {
        **$termAssigns = $this->getDoctrine()
            ->getRepository(TermAssign::class)
            ->findBy(
                ['offer'=>$id]
            );

        $terms = $this->getDoctrine()
            ->getRepository(Term::class)
            ->findBy(
                ['id'=>$terms->getId()]
            );**

        $conditions = $this->getDoctrine()
            ->getRepository(Condition::class)
            ->findAll();

        return $this->render('offer/show.html.twig', [
            'offer' => $offer,
            'terms'=> $terms,
            'conditions'=>$conditions,
            'termAssigns'=>$termAssigns,
        ]);
    }

---

Related Topics