Laravel query doesn't want to select last one

Question

I tried distinct before but somehow my query won't select the last comment. He always select the oldest comment. Then i tried it with groupBy instead of distinct. But this won't work either.

My current query:

\App\Comment::limit(5)->groupBy('comments.id')
            ->orderBy('comments.id', 'desc')
            ->join('topics', 'comments.topic_id', '=', 'comments.id')
            ->select('comments.user_id', 'topics.id', 'topics.name')
            ->whereIn('topics.cat_id', $cats)
            ->where([['comments.removed', '=', false],['topics.removed', '=', false]])
            ->get();

It's pretty long. Hopfully someone can explain me why this won't work.

@avonnadozie sorry die nu bad explaination I want to select 5 topics based on the last comments. — Smokegun, Jul 25 '17 at 18:45
No wait I want to select the last comments where the topic isn't removed and is in the chosen categories — Smokegun, Jul 25 '17 at 18:46
You say you want comments, but you are selecting topics. I guess you want 5 most recent topics with the user_id of the latest comment. Please explain exactly what you want. — Paul Spiegel, Jul 25 '17 at 20:47

Victor Anuebunwa · Accepted Answer · 2017-07-26T15:56:08.633

1

Errors found so far

groupBy is not needed
Your join statement join('topics', 'comments.topic_id', '=', 'comments.id') should be joining columns in table comments and topics

Fix

\App\Comment::select('comments.user_id', 'topics.id', 'topics.name')
    ->join('topics', 'comments.topic_id', 'topics.id')
    ->whereIn('topics.cat_id', $cats)
    ->where('comments.removed', false)
    ->where('topics.removed', false)
    ->latest('comments.id')
    ->limit(5)
    ->get();

PS: latest('comments.id') is handy for orderBy('comments.id', 'desc')

UPDATE

To get the latest comment for at most 5 recently updated topics, try this

\App\Comment::select('comments.user_id', 'topics.id', 'topics.name')
    ->from(DB::raw("(select * from comments where removed = 0 order by id desc) as comments"))
    ->join('topics', 'comments.topic_id', 'topics.id')
    ->whereIn('topics.cat_id', $cats)
    ->where('topics.removed', false)
    ->groupBy('topics.id')
    ->limit(5)
    ->get();

edited Jul 26 '17 at 15:56

answered Jul 25 '17 at 19:12

Victor Anuebunwa

2,553
2
24
34

This solution looks clean but gives every latest comment also if those 2 are in 1 topic. it also needs to be filtert on comments.topic_id – Smokegun Jul 25 '17 at 19:31
If you want to return only one comment per topic, then group by `topics.id` – Victor Anuebunwa Jul 25 '17 at 19:43
Then that problem is back then i get the oldest comment instead of the newest one. – Smokegun Jul 25 '17 at 19:44
Have you tried the solution? Having fixed the join and grouping by `topics.id` instead of `comments.id`, the output should change – Victor Anuebunwa Jul 25 '17 at 19:48
I think this trend has what you are looking for [MySQL order by before group by](https://stackoverflow.com/questions/14770671/mysql-order-by-before-group-by) – Victor Anuebunwa Jul 25 '17 at 19:56
Looking in to that but can't really get it to work :( – Smokegun Jul 25 '17 at 20:03
You can create the subquery like this `->from(DB::raw("(select * from comments order by id desc) as comments"))`, see this sample code https://pastebin.com/PawTLQyw – Victor Anuebunwa Jul 25 '17 at 23:02

score 0 · Answer 2 · edited Mar 10 '18 at 07:52

0

Try to use whereHas. You should do something like this:

\App\Comment::where('removed', false)
            ->whereHas('topic', function($q) use ($cats) {
                $q->where('removed', false)
                  ->whereIn('cat_id', $cats);
            })
            ->limit(5)
            ->orderBy('id', 'desc')->get();

To achieve this, you must have the relations established by functions, such as topics (ie: hasMany) in Comment model.

edited Mar 10 '18 at 07:52

halfer

19,824
17
99
186

answered Jul 25 '17 at 18:58

Laerte

7,013
3
32
50

Call to undefined method Illuminate\Database\Query\Builder::topics() the relations are there commet has a belongs to topic and topic has a hasmany comments – Smokegun Jul 25 '17 at 19:36
If comment belongs to topic, try only `whereHas('topic', function($q) {`. – Laerte Jul 25 '17 at 19:53
whereIn('topics.cat_id', $cats) topics.cat_id doesn;t exist there because join isn;t use and if i add whereIn to the $q it can't reach the variable $cats? – Smokegun Jul 25 '17 at 19:56
I've edited the answer, you must use `use ($cats)` to make it available in the function. Try it again, please – Laerte Jul 25 '17 at 20:09

Laravel query doesn't want to select last one

2 Answers2