1

So i've seen many subjects about my issue (greatest-n-per-group), but it seems like i just can't make it work with the queryBuilder! (for example : post)
I'm running symfony 4.2.2

Entities

-------------                    -------------
News                             Taxonomy
-------------                    -------------
id                               id
title                            name
category (ManyToOne Taxonomy)    value
status (ManyToOne Taxonomy)
publication_date

Category can take the following values: tech / sports / games .. and so on.
Status can take the following values: draft / scheduled / published.

What i want

In my NewsRepository using the queryBuilder, i would like to return the latest published news of each category.

What i ended up doing

return $this->createQueryBuilder('a')
            ->select('n news', 'n.publicationDate pubDate')
            ->leftJoin('n.category', 'c')
            ->addSelect('c.value category')
            ->leftJoin('n.status', 's')
            ->addSelect('s.value status')
            ->andWhere('s.value = :st')
            ->setParameter('st', Taxonomy::WORKFLOW_PUBLISHED)
            ->orderBy('n.category, n.datePublication', 'DESC')
            ->getQuery()
            ->getResult();

Taxonomy::WORKFLOW_PUBLISHED being a class constant storing : 'published' Then in a service i'm just iterating over the results returned by the previous method and extracting the latest news per category.
I dislike this temp solution, so if anyone could help me with the queryBuilder that would be great !

+ Additional information

I tried with a groupby on categories, but it wasn't returning the latest values.
If possible, i'm looking for good performance as well.

Elbarto
  • 1,213
  • 1
  • 14
  • 18

3 Answers3

1

If you see the original post: Doctrine Query Language get Max/Latest Row Per Group you will need a self join for your News entity in order to organize your desired data (latest news per category), after tweaking original post your query builder might look like

return $this->createQueryBuilder('a')
            ->select('n news', 'n.publicationDate pubDate')
            ->leftJoin(
                'AppBundle\Entity\News',
                'n1',
                'WITH',
                'n.category = n1.category AND n.datePublication < n1.datePublication'
            )
            ->leftJoin('n.category', 'c')
            ->addSelect('c.value category')
            ->leftJoin('n.status', 's')
            ->addSelect('s.value status')
            ->where( 'n1.datePublication IS NULL' )
            ->andWhere('s.value = :st')
            ->setParameter('st', Taxonomy::WORKFLOW_PUBLISHED)
            ->orderBy('n.category, n.datePublication', 'DESC')
            ->getQuery()
            ->getResult();
M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
0

since group by does not care about order of data you can not do this by one query.

it is better to use group by category and select max(n.id) then have another query to find relevant news by these ids

Majid Mohsenifar
  • 429
  • 4
  • 9
  • hi, im aware of this possibility, but i would like a solution with the querybuilder since it's the reason why i am struggeling. – Elbarto Mar 29 '19 at 15:19
0

have you tried groupBy in your query or in your form ?

Maybe the 'group_by' options in form will do the job :

'query_builder' => function(NewsRepository $r)use($options)
                {
                    return $r->yourQuery($options['opt']);
                },
'group_by' => function(News $news)
                {
                    return $news->getCategory()->getName();
                },

I mean, if you use orderBy in your query to get the latest news, and use the 'group_by' in the form by Category name it will works.

Get the order by query_builder, and finally sort with group_by option in the form.

Zayders
  • 153
  • 1
  • 9