jeudi 1 août 2019

Laravel get latest records with group by with multiple column

Hello I'm new in Laravel I'm stuck in a complex query with Laravel eloquent

Here is my messages table structure

---|-----------|-------------|--------
ID | sender_id | receiver_id | message
---|-----------|-------------|--------
 1 |   1       |   2         | Hello 
 2 |   1       |   2         | Hey hello reply
 3 |   2       |   1         | Good
 4 |   3       |   2         | Hey GOOD Morning
 5 |   3       |   2         | good night

I want to retrieve recent chat from messages table either user is sender or receiver I want to retrieve with a groupBy using multiple columns and with latest only one message

Like

-----------|-------------|---------------
 sender_id | receiver_id | message
-----------|-------------|---------------
   2       |   1         | Good
   3       |   2         | good night

Here is my eloquent query

$user = auth()->user(); //based on logged in user either sender or reciever
Message::where('user_id', $user->id)
        ->orWhere('receiver_id', $user->id)
        ->select('user_id','receiver_id')
        ->groupBy('user_id','receiver_id')
       ->get();

Result

all: [
   App\Models\Message {
        user_id: 1,
        receiver_id: 2,
   }, // don't want this record
   App\Models\Message {
        user_id: 2,
        receiver_id: 1,
   },
   App\Models\Message {
        user_id: 3,
        receiver_id: 2,
   },
],

If I try to use latest() method then it's showing error timestamp is there in a table

Illuminate/Database/QueryException with message 'SQLSTATE[42000]:

Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column

I don't want to update mysql_strict mode in config/database.php

it's not working properly can anyone help me or any good suggestion or idea

Any help will appreciate



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire