在Laravel中对复杂查询进行orderBy之后的GroupBy [英] GroupBy after orderBy for complicated query in Laravel

问题描述

这是我的代码，用于为每次聊天获取消息并将其分组

  $ query = DB :: table('chats');$ otherUserId = $ request-> input('loadId');if($ otherUserId == 0){$ query-> join('users as u1'，'u1.id'，'='，'chats.user1_id');$ query-> join('users as u2'，'u2.id'，'='，'chats.user2_id');$ query-> join('messages'，'messages.chat_id'，'='，'chats.id');$ query-> where('chats.user1_id'，'='，'{$ userId}');$ query-> orWhere('chats.user2_id'，'='，'{$ userId}');$ query-> select('messages.chat_id'，'messages.from_id'，'u1.id as user1_id'，'u2.id as user2_id'，'messages.created_at'，'messages.body'，'messages.read'，'u1.name作为user1_name'，'u2.name作为user2_name'，'u1.picture_url作为user1_pic'，'u2.picture_url作为user2_pic');$ query-> orderBy('messages.created_at'，'DESC');$ query-> groupBy('chat_id');$ messages = $ query-> paginate(4); 

当我运行查询时，结果与预期不符.我已经尝试了很多，但是找不到解决方法.我也尝试了此解决方案，但无法使其正常工作.在使用Eloquent(Laravel)分组之前进行排序

请帮助我，我已经浪费了很多时间来尝试解决问题.预先感谢您的帮助.

解决方案

感谢大家的帮助.终于我解决了，我从另一个帖子 https://stackoverflow.com/a/7745635/6279602 上得到了帮助.a>它适用于联接，而不适用于group by，因为group by不支持排序.

Here is my code for fetching messaging and grouping them for each chat

$query = DB::table('chats');

    $otherUserId= $request->input('loadId');
    if($otherUserId==0){
        $query->join('users as u1', 'u1.id', '=', 'chats.user1_id');
        $query->join('users as u2', 'u2.id', '=', 'chats.user2_id');
        $query->join('messages', 'messages.chat_id', '=', 'chats.id');
        $query->where('chats.user1_id', '=', '{$userId}');
        $query->orWhere('chats.user2_id', '=', '{$userId}');
        $query->select('messages.chat_id', 'messages.from_id', 'u1.id as    user1_id', 'u2.id as user2_id', 'messages.created_at', 'messages.body', 'messages.read', 'u1.name as user1_name', 'u2.name as user2_name', 'u1.picture_url as user1_pic', 'u2.picture_url as user2_pic');

        $query->orderBy('messages.created_at ', 'DESC');
        $query->groupBy('chat_id');
        $messages = $query->paginate(4);

When I run the query the result is not as expected OrderBy comes after group by. I've tried a lot but could't find and solution. I tried this solution also but couldn't make it work. Order By before Group By using Eloquent (Laravel)

Please help me I've lost a lot of time trying to sort it out. Thanks in advance for helping me.

解决方案

Thanks all for helping me. At last I solved it, I got help from this answer from another post https://stackoverflow.com/a/7745635/6279602 it works with joins not with group by because group by does not support ordering.

这篇关于在Laravel中对复杂查询进行orderBy之后的GroupBy的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！