主义查询在哪里-多对多 [英] Doctrine query WHERE IN - many to many
问题描述
我正在Symfony2中建立一个酒店网站。每家酒店都可以提供多种膳食选择，例如自助餐饮，全包式等。
I am building out a hotel website in Symfony2. Each hotel can provide many board basis options such as Self Catering, All-Inclusive etc.
在我的搜索表中，用户可以按所有常用字段（例如位置）进行过滤，价格，星级和董事会依据。董事会基准是一个多选复选框。
On my search form the users can filter by all of the usual fields such as location, price, star rating and board basis. Board basis is a multiple select check box.
当用户选择多个董事会基准选项时，我目前正在以这种方式进行处理...（这会引发错误）
When a user selects multiple board basis options, I am currently handling it in this way... (which is throwing errors)
$repo = $this->getDoctrine()->getRepository("AppBundle:Accommodation");
$data = $form->getData();
$qb = $repo->createQueryBuilder("a")
->innerJoin("AppBundle:BoardType", "b")
->where("a.destination = :destination")
->setParameter("destination", $data['destination'])
->andWhere("a.status = 'publish'");
if (count($data['boardBasis']) > 0) {
$ids = array_map(function($boardBasis) {
return $boardBasis->getId();
}, $data['boardBasis']->toArray());
$qb->andWhere($qb->expr()->in("a.boardBasis", ":ids"))
->setParameter("ids", $ids);
}
这是酒店实体的财产声明
Here is the property declaration on a hotel entity
/**
* @ORM\ManyToMany(targetEntity="BoardType")
* @ORM\JoinTable(name="accommodation_board_type",
* joinColumns={@ORM\JoinColumn(name="accommodation_id", referencedColumnName="id")},
* inverseJoinColumns={@ORM\JoinColumn(name="board_type_id", referencedColumnName="id")}
* )
*/
private $boardBasis;
我当前遇到的错误是：
[语义错误]行0，'boardBasis I'附近的col 177：错误：无效的PathExpression。预期为StateFieldPathExpression或SingleValuedAssociationField。
[Semantical Error] line 0, col 177 near 'boardBasis I': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.
提交表单并在董事会类型上使用 var_dump ：
On submitting the form and using var_dump on the board types I am getting:
object(Doctrine\Common\Collections\ArrayCollection)[3043]
private 'elements' =>
array (size=2)
0 =>
object(AppBundle\Entity\BoardType)[1860]
protected 'shortCode' => string 'AI' (length=2)
protected 'id' => int 1
protected 'name' => string 'All-Inclusive' (length=13)
protected 'description' => null
protected 'slug' => string 'all-inclusive' (length=13)
protected 'created' =>
object(DateTime)[1858]
...
protected 'updated' =>
object(DateTime)[1863]
...
1 =>
object(AppBundle\Entity\BoardType)[1869]
protected 'shortCode' => string 'BB' (length=2)
protected 'id' => int 2
protected 'name' => string 'Bed & Breakfast' (length=15)
protected 'description' => null
protected 'slug' => string 'bed-breakfast' (length=13)
protected 'created' =>
object(DateTime)[1867]
...
protected 'updated' =>
object(DateTime)[1868]
...
我似乎为了找到该查询的正确语法，我过去做过几次（每次都很痛苦），但我只是不记得它是如何完成的。我试过不映射ID，直接将 ArrayCollection 传入。
I cannot seem to find the correct syntax for this query, I have done a few times in the past (and it was a pain each time), but I just cannot remember how it is done. I have tried without mapping the ID's, passing the ArrayCollection directly in.
我唯一想到的是现在是将其切换为使用 createQuery 并使用DQL看看是否有任何区别。
The only thing I can think of at the moment is to switch it to using createQuery and using DQL and see if that makes any difference.
任何帮助对此问题将不胜感激，谢谢
Any help with this issue would be appreciated, Thank you
推荐答案
在我看来，您加入的计划还不完全。您缺少描述要加入哪个字段的语句：
It looks to me like you're join is not fully complete. You were missing a statement describing what field to join on:
$qb = $repo->createQueryBuilder("a")
->innerJoin("AppBundle:BoardType", "b")
->where("a.boardBasis = b.id")
...
或者您可以这样加入：
$qb = $repo->createQueryBuilder("a")
->innerJoin("a.boardBasis", "b")
...
然后，您可以添加 WHERE IN 语句，如下所示：
Then you can do add your WHERE IN statement like so:
$qb->andWhere('b.id IN (:ids)')
->setParameter('ids', $ids);
这篇关于主义查询在哪里-多对多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
