Doctrine many to many left join

Question

I have a problem with creating a query which should return cost centers not assigned to budget.

Database structure:

**Cost_center:**
+------+-----------+
|  id  |   title   |
+------+-----------+
| (PK) | (VARCHAR) |
+------+-----------+
\/
One 
to 
many
\/
**Budget_operation_scope_cost_center:**
+----------------+---------------------------+
| cost_center_id | budget_operation_scope_id |
+----------------+---------------------------+
| (FK)           | (FK)                      |
+----------------+---------------------------+
\/
Many 
to 
one
\/
**Budget_operation_scope:**
+------+-----------+-----------+
|  id  |   title   | budget_id |
+------+-----------+-----------+
| (PK) | (VARCHAR) | (FK)      |
+------+-----------+-----------+
\/
Many 
to 
one
\/
**Budget:**
+------+-------+
|  id  | year  |
+------+-------+
| (PK) | (INT) |
+------+-------+

Managed to do a query which returns assigned to budget cost centers list:

$query = $this->getEntityManager()
    ->createQueryBuilder()
    ->select('costCenter')
    ->from('ResourcesBundle:CostCenter', 'costCenter')
    ->leftJoin('costCenter.budgetOperationScope', 'budgetOperationScope')
    ->where('budgetOperationScope.budgetId = :budget')
    ->setParameter('budget', $budget)
    ->getQuery()->getResult();

Question: how to get cost centers, which are not assigned to budget?

Answer 1

This line is constraining your query too early and effectively making your left join a join:

->where('budgetOperationScope.budgetId = :budget')

You can move it into your left join like so:

->leftJoin('costCenter.budgetOperationScope', 'budgetOperationScope', 'WITH' 'budgetOperationScope.budgetId = :budget')

This way you will now get null rows for budgetOperationScope when a cost center has no budget.

So you can effectively do:

->where('budgetOperationScope IS NULL')

All together:

$query = $this->getEntityManager()
    ->createQueryBuilder()
    ->select('costCenter')
    ->from('ResourcesBundle:CostCenter', 'costCenter')
    ->leftJoin('costCenter.budgetOperationScope', 'budgetOperationScope', 'WITH' 'budgetOperationScope.budgetId = :budget')
    ->where('budgetOperationScope IS NULL')
    ->setParameter('budget', $budget)
    ->getQuery()->getResult();