[FIXED] Doctrine INDEX BY foreign key

Issue

I am trying to get summary data for articles from article_stats table indexed with article ids.

The query builder looks like this:

$qb = $this->articlesStatsRepository->createQueryBuilder('ass');
$qb->select('SUM(ass.pageviews)')
    ->indexBy('ass', 'ass.article') // this doesnt work
    ->groupBy('ass.article');

$articleStats = $query->getResult();

This results in query

SELECT SUM(ass.pageviews) 
FROM AppBundle\Entity\ArticleStats ass 
INDEX BY ass.article 
GROUP BY ass.article

With an error of:

[Semantical Error] near 'article GROUP': 
Error: Invalid PathExpression. Must be a StateFieldPathExpression.

I've tried ->indexBy('ass', 'IDENTITY(ass.article)') but that didn't work either.

So how can I select data from database indexed by foreign key?

Solution

Without seeing the exact composition of the entities and their relationships, it's hard to give a precise answer. Even so, if ass.article is an entity, and not the numeric article ID, the query will not work. INDEX BY must be used with an ORM column.

An example is included below:

SELECT a.id, SUM(c.visits) AS visits
FROM Customer c
LEFT JOIN c.account a
INDEX BY a.id
GROUP BY a.id

$results will contain an associative array of entities indexed by name. To do this with a foreign key, you may have to do a join first, or use nested DQL. It is also possible that INDEX BY will not work in the exact way that you want. A workaround would look like this (includes ID as part of associative array instead of as array key):

SELECT a.id, SUM(c.visits) AS visits
FROM Customer c
LEFT JOIN c.account a
GROUP BY a.id

If you still cannot get this working after reading over this answer, I'd advise providing additional information about the entities and any relevant relationships.

Answered By - webjawns.com