doctrine condition on associative table when joini

Question

I have many to many relationship between Books <--> Account

How can i write DQL with condition on associative table?

for example 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

Answer 1

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);