I am trying to build a query using Doctrine but I struggle a bit with DQL and ManyToMany relationships.
I have an entity Business, Client, Manager and Building.
Each Business is related to a Business, can have many Manager(s), and each Manager is related to a Client.
Also, each Business is related to Clients with a ManyToMany relationship. Here is a scheme that can help to understand the relationships :
I would like to build a query that lists all clients related to a business, with joins from business to clients, but who are not directly in the business-client ManyToMany relationship.
The point to this query is that I want to list the clients related to a Business in a select, and the user choose which client he wants to add to the Business, so it creates an entry in the ManyToMany relationship between Business and Client. I want to hide the clients already added to the Business so someone can't add multiple times a Client to a Business.
Here is what I would like to do using SQL :
SELECT * FROM client c
INNER JOIN manager m ON c.id = m.client_id
INNER JOIN building_manager bm ON m.id = bm.manager_id
INNER JOIN building b ON bm.building_id = b.id
INNER JOIN business bs ON b.id = bs.building_id
LEFT JOIN business_client bc ON bs.id = bc.business_id
WHERE bs.id = ?
AND bc.business_id IS NULL;
Using Doctrine, I tried to achieve a similar result, using this in my ClientRepository file :
public function findByBuilding($id) {
return $this->createQueryBuilder('c')
->innerJoin('App\Entity\Manager', 'm', 'WITH', 'c.id = m.client')
->join('m.buildings', 'b')
->leftJoin('c.businesses','bs')
->andWhere('b.id = :val')
->andWhere('bs.id IS NULL')
->setParameter('val', $id)
->getQuery()
->getResult();
}
It seems like the line ->andWhere('bs.id IS NULL')
because it is understood as WHERE business.id IS NULL
and I would like something like WHERE business_client.business_id IS NULL