Doctrine query WHERE IN - many to many

Question

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;

The error I am currently getting is:

[Semantical Error] line 0, col 177 near 'boardBasis I': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.

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]
              ...

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.

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

Answer 1

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")
    ...

Or you could join like that:

$qb = $repo->createQueryBuilder("a")
    ->innerJoin("a.boardBasis", "b")
    ...

Then you can do add your WHERE IN statement like so:

$qb->andWhere('b.id IN (:ids)')
    ->setParameter('ids', $ids);