Reputation: 197
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 :
An account contains multiple users
An invoice is always linked to an account AND a company
A CompanyType is an Object, not a String
The invoices I want to get are the ones linked to companies the user has right on (= the user has a companyType that the company itself has)
Is there anything else that I can use with the query builder to do this ?
Upvotes: 3
Views: 5105
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