CODEWITHSUNDEEP
phpmysqlsqllaravel
Soul Coder
Soul Coder

Reputation: 804

how to group by in db query builder to get the unique value?

Can someone help me to convert the below mysql to laravel db query builder or eloquent?Here is my mysql 

SELECT max(created_at), jobseeker_id,call_reason,type_of_call
FROM calllogs
GROUP BY jobseeker_id
ORDER BY created_at DESC;

Here is my tries, but no luck yet.

             $jobseekers =DB::table('calllogs')
            ->select(DB::raw("max('created_at'),jobseeker_id"))
                 ->groupBy('jobseeker_id')  
                 ->orderBy('created_at','desc')
                 ->get();
                 dd($jobseekers);

Please help me out.

Upvotes: 0

Views: 4316

Answers (3)

Mahesh Chinnala
Mahesh Chinnala

Reputation: 1

ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'myapp.calllogs.created_at'

Two solution for that error

1.In config/database.php file make sql strict mode to false.

(or) 2. IN groupBy Clause add all your table colums.

example:

->groupBy('jobseeker_id','jobseeker_name','jobseeker_email')

Upvotes: 0

Hashan
Hashan

Reputation: 180

DB::table(calllogs)
->select(DB::raw('max(created_at) as max_created_at'), 
'jobseeker_id','call_reason','type_of_call')
->groupBy('jobseeker_id')
->orderBy('max_created_at', 'DESC')
->get();   =>As a array.
->first(); => As a object.

Additionally, If you are using table joins see below.

->join('table_1','table_1.id','=','table_2.table_1_id')
->leftjoin('table_1','table_1.id','=','table_2.table_1_id')
->rightjoin('table_1','table_1.id','=','table_2.table_1_id')

Upvotes: 1

HiKangg
HiKangg

Reputation: 261

$jobseekers =DB::table('calllogs')
  ->select(DB::raw("max(created_at),jobseeker_id"))
  ->groupBy('jobseeker_id')  
  ->orderBy('created_at','desc')
  ->get();

Try with this code. Only delete ' ' in max. It will work well.

Upvotes: 2

Related Questions