Symfony approach to displaying aggregate entity data

Question

I have 3 entities Expertise, Person and Tag, related through a join entity, AssociatedTag which has a composite primary key (expertise_id, tag_id, person_id). This works correctly.

Because more than one Person can add the same Tag to a particular Expertise, simply displaying each tag string can result in duplicates. If I weren't working in Symfony, I'd just do an aggregate query similar to this:

SELECT 
    t.tag, a.tag_id
    a.expertise_id,
    COUNT(a.person_id) AS tagcount,
    SUM(CASE
        WHEN a.person_id = ? THEN 1
        ELSE 0
    END) AS has_curuser
FROM
    expertise_tags a
        JOIN
    tag t ON (a.tag_id = t.id)
WHERE
    a.expertise_id = ?   #397
GROUP BY t.tag , a.expertise_id;

which would produce data like this:

foobe   9   397 1   0
neato   7   397 2   1
pita    8   397 1   0

and then output with a simple loop and conditions like has_curuser == TRUE and tagcount > 1 to control the output options.

I get the impression with Symfony, that there ought to be a class for holding the aggregate results or perhaps there should be a data transformer or Twig extension that takes unaggregated results and aggregates them (though that seems inefficient).

What is the correct Symfony approach to this situation?

Little misunderstood the question, the problem is to perform a query, or output the result? — Alexey B., Aug 31 '13 at 04:12
@forgottenbas: If it is correct to do the query, where should it go (perhaps the controller?) and how to do it; if it is not correct to do the query, how to output the `AssociatedTag` collection so that the aggregate characteristics can be taken into account. — dnagirl, Aug 31 '13 at 13:50
One possible solution is to use native queries. I describe it in my answer. Yes, you can run query in controller, or in entity repository or from custom service. — Alexey B., Aug 31 '13 at 15:07

score 0 · Accepted Answer · answered Aug 31 '13 at 15:06

Due to the fact that the doctrine DQL supports a limited part of the functionality of the native SQL language for complex queries, you can use native queries.

With NativeQuery you can execute native SELECT SQL statements and map the results to Doctrine entities or any other result format supported by Doctrine.

Symfony approach to displaying aggregate entity data

1 Answers1