CODEWITHSUNDEEP
phpsymfonydoctrine
Tibo
Tibo

Reputation: 197

Doctrine Query Builder IN clause with multiple values

I have a User entity which has a ManyToMany relationship with companyType :

/**
 * @ORM\ManyToMany(targetEntity="App\Entity\CompanyType")
 * @ORM\OrderBy({"name" = "ASC"})
 * @ORM\JoinColumn(nullable=true)
 */
private $companyTypes;

And a Company entity which can have 0 or multiple types :

/**
 * @ORM\ManyToMany(targetEntity="App\Entity\CompanyType", inversedBy="companies")
 * @ORM\OrderBy({"name" = "ASC"})
 * @ORM\JoinColumn(nullable=true)
 */
private $companyTypes;

This creates a user_companyType table in my database.

I want to build, with Doctrine queryBuilder, a query which returns if at least one element in u.companyTypes is in c.companyTypes.

I can't find how to.

Here's what I've tried :

$userCompanyTypes = array();
foreach ($user->getCompanyTypes() as $companyType) {
    $userCompanyTypes[] = $companyType;
}

$qb = $this->_em->createQueryBuilder('i')
->from(Invoice::class, 'i')
->leftJoin('i.account', 'a')
->leftJoin('i.company', 'c')
->leftJoin('c.companyTypes', 'ct')
->leftJoin('a.users', 'u')
->andWhere("c.companyTypes IN (:userCompanyTypes)") // ... search if there's a match 
->setParameter("userCompanyTypes", $userCompanyTypes);

return $qb->getQuery()->getResult();

Long story short :

Is there anything else that I can use with the query builder to do this ?

Upvotes: 3

Views: 5105

Answers (1)

Cerad
Cerad

Reputation: 48893

I don't normally like to answer these sorts of question without setting up a test case. Your error message is a bit odd but I'll give it a try. I know that the IN clause will work with individual ids. So maybe:

$userCompanyTypeIds = array();
foreach ($user->getCompanyTypes() as $companyType) {
    $userCompanyTypeIds[] = $companyType->getId();
}
$qb = $this->_em->createQueryBuilder('i')
->select('count(DISTINCT i.id)')
->from(Invoice::class, 'i')
->leftJoin('i.account', 'a')
->leftJoin('i.company', 'c')
->leftJoin('c.companyTypes', 'ct')
->leftJoin('a.users', 'u')
->andWhere("ct.id IN (:userCompanyTypeIds)") // ... search if there's a match 
->setParameter("userCompanyTypeIds", $userCompanyTypeIds);

$count = $qb->getQuery()->getSingleScalarResult();

Should at least get you a step further. Updated the answer showing how to get a total count. Never been a big fan of using the expr class even though it is quite common.

Upvotes: 4

Related Questions