OrderBy is not working while applying GroupBy Laravel

Question

I am using following query to retrieve the user feeds but ordering latest update is not working when we applying order created_at time by desc

Here i have given SQL query. Kindly suggest me solution to solve my problem

    select `users`.`id` as `uid`, `updates`.`post_id`, `updates`.`id` as `upid`, `friends`.`id` as `fid`, `updates`.`user_id`, `updates`.`privacy`, `updates`.`updated_at`, `updates`.`created_at`, `friends`.`first_user_id`, `friends`.`second_user_id`, `friends`.`friend_status`, `updates`.`update_type` from `updates` 
inner join `users` on `updates`.`user_id` = `users`.`id`
inner join `friends` on 
    CASE
        WHEN friends.first_user_id = 1
        THEN friends.second_user_id = updates.user_id
        WHEN friends.second_user_id= 1
        THEN friends.first_user_id = updates.user_id
    END
where `friends`.`friend_status` > 0 
    and `updates`.`privacy` in (1,2) 
    and `updates`.`user_id` != 1 and 
    `updates`.`deleted_at` is null 
group by `updates`.`post_id` 
order by `updates`.`created_at` desc

Laravel Code

Update::select(
        'users.id as uid', 'posts.id as pid', 'updates.post_id', 'updates.id as upid',
        'friends.id as fid', 'updates.user_id', 'updates.privacy', 'updates.updated_at', 'updates.created_at', 'posts.deleted_at', 'friends.first_user_id', 
        'friends.second_user_id', 'friends.friend_status', 'updates.update_type', 'posts.wall_user_id'
        )->join('users', 'updates.user_id', '=', 'users.id')
        ->join('posts', 'posts.id', '=', 'updates.post_id')
        ->join('friends', function($join) use ($userId){
            $join->on(DB::raw('CASE
                        WHEN friends.first_user_id = '.$userId.'
                        THEN friends.second_user_id = updates.user_id
                        WHEN friends.second_user_id= '.$userId.'
                        THEN friends.first_user_id = updates.user_id
                        END'
                    ), DB::raw(''), DB::raw(''));
        })->where('friends.friend_status', '>', 0)
        ->whereIn('updates.privacy', [1,2])
        ->where('updates.user_id', '!=', Auth::user()->id)
        ->groupBy('updates.post_id')
        ->orderBy('updates.created_at', 'DESC')
        ->get();

http://stackoverflow.com/questions/1066453/mysql-group-by-and-order-by May be this will help you — Ashish, Sep 14 '16 at 10:07
@MarkoMilivojevic tried this also but not getting expected result — azhagu, Sep 14 '16 at 10:49

score 3 · Answer 1 · edited Sep 14 '16 at 11:13

select * from(select `users`.`id` as `uid`, `updates`.`post_id`, `updates`.`id` as `upid`, `friends`.`id` as `fid`, `updates`.`user_id`, `updates`.`privacy`, `updates`.`updated_at`, `updates`.`created_at`, `friends`.`first_user_id`, `friends`.`second_user_id`, `friends`.`friend_status`, `updates`.`update_type` from `updates` 
inner join `users` on `updates`.`user_id` = `users`.`id`
inner join `friends` on 
    CASE
        WHEN friends.first_user_id = 1
        THEN friends.second_user_id = updates.user_id
        WHEN friends.second_user_id= 1
        THEN friends.first_user_id = updates.user_id
    END
where `friends`.`friend_status` > 0 
    and `updates`.`privacy` in (1,2) 
    and `updates`.`user_id` != 1 and 
    `updates`.`deleted_at` is null order by `updates`.`created_at` desc) as tmp_table 
group by `updates`.`post_id`

OrderBy is not working while applying GroupBy Laravel

1 Answers1