Which laravel query to use to sort top entries according to related table

Question

I have two models: posts and likes. Posts and likes have one-to-many relationship (so, one post has many likes). Likes model has also an isActive field which shows liking is active or passive.

I want to get (sort) top 5 posts which had received maximum "active" likes (only likes whose isActive field is true would be considered).

Which Laravel query could give me the result?

My question is sorting the post not only according to a field of a related model but also count of entries in the related table.

This is the query:

$posts = Post::selectRaw('posts.*, count(likings.id) as likes_count')
              ->leftJoin('likings', function ($join) {
                     $join->on('likings.post_id', '=', 'posts.id')
                            ->where('likings.isActive', '=', 1);
              })
              ->groupBy('posts.id')
              ->orderBy('likes_count', 'desc')
              ->take(5)
              ->get();

And this is the error:

SQLSTATE[42000]: Syntax error or access violation: 1055 'database.posts.user_id' isn't in GROUP BY 
(SQL: select posts.*, count(likings.id) as likes_count from 'posts' left join 'likings' on 'likings'.'post_id' = 'posts'.'id' and 'likings'.'isActive' = 1 group by 'posts'.'id' order by 'likes_count' desc limit 5)

Possible duplicate of [Laravel order by hasmany relationship](http://stackoverflow.com/questions/28634921/laravel-order-by-hasmany-relationship) — Tim, Nov 02 '15 at 14:38
I found some different solutions with "count *" but none of them seemed to me exact solution. I can't figure out the way. — horse, Nov 02 '15 at 14:43
Not a dublicate question. I want to sort the posts based on **count** of active likes. — horse, Nov 02 '15 at 14:49

score 1 · Answer 1 · answered Nov 02 '15 at 15:20

1

or just exequte this query

Post::with(['likes' => function ($query){ $query->where('active', 1); }]);

and sort it by php if its too hard in mysql. For ex some PostTransformer class

answered Nov 02 '15 at 15:20

Janko

202
2
11

score 0 · Answer 2 · answered Nov 02 '15 at 15:13

    Post::join(DB::raw('(select post_id, count(post_id) as number from likes) as likes_count ON posts.id = likes_count.post_id where likes_count.active = 1'), null)->orderBy('likes_count.number', 'desc')->limit(5);

I wrote that without checking this out, so don't hate

Jarek Tkaczyk · Accepted Answer · 2015-11-06T21:02:17.690

0

Post::selectRaw('posts.*, count(likes.id) as likes_count')
    ->leftJoin('likes', function ($join) {
        $join->on('likes.post_id', '=', 'posts.id')
             ->where('likes.is_active', '=', 1);
    })
    ->groupBy('posts.id')
    ->orderBy('likes_count', 'desc')
    ->take(5)
    ->get();

or subselect:

Post::select('*')->selectSub(function ($q) { 
   $q->from('likes')
     ->whereRaw('likes.post_id = posts.id')
     ->where('is_active', 1)
     ->selectRaw('count(*)');
 }, 'likes_count')
 ->orderBy('likes_count', 'desc')
 ->take(5)
 ->get();

edited Nov 06 '15 at 21:02

answered Nov 05 '15 at 20:53

Jarek Tkaczyk

78,987
25
159
157

Thank you Jarek. I tried it, but gives this error: **posts.user_id isn't in GROUP BY (syntax error or access vioaltion 1055)**. There is no _user_id_ in the query, but in the _posts_ table it exists and gives the owner of the post. – horse Nov 06 '15 at 10:05
Yes :) Here it mentions ONLY_FULL_GROUP_BY mode set. Can it be the reason of error? http://stackoverflow.com/questions/27878604/sql-error-database-table-field-isnt-in-group-by – horse Nov 06 '15 at 12:10
And here is that saying "you need to have a full group by". http://stackoverflow.com/questions/25800411/mysql-isnt-in-group-by – horse Nov 06 '15 at 12:16
Paste the code you execute and error you are getting. I won't try to guess. – Jarek Tkaczyk Nov 06 '15 at 17:45
@user2356198 It's your mysql config thing: http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_only_full_group_by You can use subselect instead - see the edit – Jarek Tkaczyk Nov 06 '15 at 21:02

Which laravel query to use to sort top entries according to related table

3 Answers3