Filtering Doctrine Entities by their Associations

Question

I have an Entity called Company. The associations are mapped like this:

manyToOne:
    headCompany:
        targetEntity: CompanyEntity
        inversedBy: headCompany
oneToMany:
    subsidiaries:
        targetEntity: CompanyEntity
        mappedBy: headCompany

Everything works fine. A company can be subsidiary of another and/or have its own subsidiaries.

Now, I need to filter Companies that ARE head companies of others, or companies that have subsidiaries.

I can do that directly with SQL:

SELECT c.id, COUNT(s.headCompany_id) subsidiaries FROM companies c LEFT JOIN companies s ON s.headCompany_id = c.id GROUP BY c.id HAVING subsidiaries > 0

Resulting something like this:

+----+--------------+
| id | subsidiaries |
+----+--------------+
| 1  | 3            |
+----+--------------+
| 5  | 2            |
+----+--------------+

Then, companies 1 and 5 would fit my filter because they both have subsidiaries.

How can I achieve this same result using Doctrine Query Builder?

My attempts were:

$query = $qb->select('e')
    ->from('CompanyEntity', 'e')
    ->orderBy('e.id', 'ASC')
    ->join('e.headCompany', 'h')
    ->addSelect("COUNT(h.id) as subsidiaries")
    ->groupBy("e.id")
    ->having("subsidiaries > 0");

This one returns the subsidiaries, not the head companies.

->where($qb->expr()->isNotNull("e.subsidiaries"))

And this is not allowed by Doctrine apparently.

M Khalid Junaid · Accepted Answer · 2017-10-29T08:48:40.527

0

How about reverse the join clause, select company and join with subsidiaries then apply filter on count of subsidiaries related to each company

$query = $qb->select('h,s')
    ->from('CompanyEntity', 'h')
    ->join('h.subsidiaries', 's')
    ->addSelect("COUNT(s.id) AS HIDDEN subsidiaries")
    ->groupBy("h.id")
    ->orderBy('h.id', 'ASC')
    ->having("subsidiaries > 0");

As you are using sqlmode =>only_full_group_by you cannot select all columns of entity unless they are present in group by, so have to choose in select() which columns to return.

$query = $qb->select('h.id')
    ->from('CompanyEntity', 'h')
    ->join('h.subsidiaries', 's')
    ->addSelect("COUNT(s.id) AS HIDDEN subsidiaries")
    ->groupBy("h.id")
    ->orderBy('h.id', 'ASC')
    ->having("subsidiaries > 0");

As per official documentation "ONLY_FULL_GROUP_BY Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns."

edited Oct 29 '17 at 08:48

answered Oct 27 '17 at 12:02

M Khalid Junaid

63,861
10
90
118

Thanks for your time but It didn't work: `SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #12 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'table.c1_.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by`. Is it correct to join `subsidiaries` in this context? It's not an actual column, just an Doctrine association. – Vinícius Oct 27 '17 at 13:57
Same problem :-( `SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #12 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'table_dev.c0_.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by` – Vinícius Oct 28 '17 at 23:52
@Vinícius I have updated my answer as you are using ONLY_FULL_GROUP_BY mode – M Khalid Junaid Oct 29 '17 at 08:49
1

Your last update worked flawlessly! Thank you so much! Sidenote: I've changed `$query = $qb->select('h.id')` to `$query = $qb->select('h')` to make Doctrine fill all the entity fields. – Vinícius Oct 30 '17 at 09:36

score 0 · Answer 2 · answered Oct 27 '17 at 13:54

0

You should be able to use an order by case, kinda like this:

$query = $qb->select('e')
    ->from('CompanyEntity', 'e')
    ->join('e.headCompany', 'h')
    ->addSelect("(CASE WHEN COUNT(h.id) > 0 THEN 1 ELSE 0 END) as sort_sub")
    ->groupBy("e.id")
    ->orderBy("sort_sub");

You can read more about it in this thread: How to ORDER BY CASE in Doctrine2 (Symfony2)

answered Oct 27 '17 at 13:54

Tobias Kristensen

11
3

Removing the `->orderBy("sort_sub");` that was giving errors, this method returns the companies that _ARE_ subsidiaries, not companies that _HAVE_ subsidiaries, just like my first attempt, unfortunately. Thanks for your time. – Vinícius Oct 27 '17 at 14:30

Filtering Doctrine Entities by their Associations

2 Answers2