CODEWITHSUNDEEP
symfonydoctrine-ormmany-to-manydql
YuraV
YuraV

Reputation: 103

doctrine condition on associative table when joining many to many

I have a many-to-many relationship between 2 entities: Books <--> Account

How can I write theDQL with condition on the associative table?

This is an example, written in SQL:

SELECT books.*, account.id FROM books 
LEFT JOIN account_books ON books.id = account_books.books_id AND account_books.account_id = 17 
LEFT JOIN account ON account.id = account_books.account_id

Upvotes: 0

Views: 386

Answers (1)

Preciel
Preciel

Reputation: 2837

In your BooksRepository, should be something like that

public function getAccountBooks($accountId) {
    return $this->getEntityManager()
                ->createQueryBuilder()
                ->select("b.paramA", "b.paramB", "b.paramC", "acc.paramA")
                ->from('AppBundle:Books', 'b')
                ->leftJoin('AppBundle:AccountBooks', 'acb', 'WITH', 'b.id=acb.id')
                ->leftJoin('AppBundle:Account', 'acc', 'WITH', 'acc.id=acb.id')
                ->where("accountId = :accountId")
                ->setParameters(array(
                    new Parameter('accountId', $accountId),
                ))
                ->getQuery()
                ->getArrayResult();
}

Do note that DQL use entity parameters and not SQL columns name

And then in your action function:

$accountBooks=$em->getRepository('AppBundle:Books')
          ->getAccountBooks($accountId);

Upvotes: 1

Related Questions