advanced query grouping

Hi all,

this is my first question on laracasts, I hope I'm doing it right :-)

what I want: query my DB to get the lastest featured news from 3 different topics
what I did so far:

News::select(DB::raw('*, MAX(published_at)'))
  ->where('featured', 1)
  ->where('state', 'published')
  ->where('published_at', '<=', Carbon::now())
  ->groupBy(DB::raw('primary_topic_id DESC'))
  ->orderBy('published_at', 'DESC')
  ->limit(3)
  ->get();

however, the resulting query does not deliver what I want..
the following manual query does the trick:

SELECT *
FROM news n1
where `featured` = 1 and `state` = 'published' and `published_at` <= NOW() 
AND published_at = (SELECT MAX(published_at) FROM news n2 WHERE n1.id = n2.id)
GROUP BY primary_topic_id DESC
ORDER BY published_at DESC
LIMIT 3

how to do that with eloquent?

burlresearch

8 years ago

Level 40

That's a tricky query - and I'm not even sure it would work in general. Even if you try running the raw SQL query with DB::select($query) I think you may run into trouble.

It may be dependent on the MySQLServer settting for sql_mode=only_full_group_by being off, which is not really portable. I'm not 100% sure about this, but it doesn't seem to work for me, so I'll leave it at that.

I do have an eloquent solution for you though. It's not perfect as the main query actually pulls all of the News items (which you could limit in some way, maybe ->whereBetween('published_at',...)), but here is what I would do:

        $topics = [];
        $news   = News::where('state', 'published_at')
            ->where('published_at', '<=', now())
            ->whereFeatured(1)
            ->orderByDesc('published_at')
            ->get()
            ->filter(function ($v) use (&$topics) {
                $found    = in_array($v->topic, $topics);
                $topics[] = $v->topic;
                return !$found;
            })
            ->take(3)
        ;

1 like

Please or to participate in this conversation.