多对多连接时关联表上的主义条件

问题描述

我与图书之间有很多关系<->帐户

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

如何在关联表上写有条件的DQL?

How can i write DQL with condition on associative table?

例如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

推荐答案

在您的BooksRepository中，应该是这样的

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

请注意，DQL使用实体参数，而不使用SQL列名称

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

然后在您的action函数中:

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

这篇关于多对多连接时关联表上的主义条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！