How to write mysql query in laravel 5.4?

Question

I am new to laravel and i want to write mysql query in laravel 5.4. query is like :

note: avoid column names..

SELECT * 
FROM (SELECT DISTINCT * 
      FROM messages 
      WHERE (from_id=1 OR to_id=1) 
ORDER BY created DESC) as m 
GROUP BY `from_id` 

I tried but gives error.

$messages = DB::table('messagetbl')
                ->select('*')
                ->Where(function($query) use ($userid){
                    $query->distinct()
                    ->where('senderid',$userid)
                    ->orWhere('receiverid',$userid)
                    ->orderBy('datetime','desc');
                 })
                ->groupBy('senderid')
                ->get();

Error:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.messagetbl.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select * from messagetbl where (senderid = 8 or receiverid = 8) group by senderid)

Thanks in advance.

Answer 1

I believe something like this:

  DB::table('messages')
        ->select('*') 
        ->where('form_id', '=', 1)
        ->orWhere('to_id', '=', 1)
        ->orderBy('created', 'desc')
        ->groupBy('form_id')
        ->distinct()
        ->get();