Category : query-builder

I am new Laravel and I just want to group my array by key. Here’s what I have done so far: Code: $vehicles = DB::select(‘call get_vehicles’); return $vehicles; Return Value: [ { "vhc_id":"001", "vhc_make":"Toyota", "vhc_model":"HiluxSR" }, { "vhc_id":"001", "vhc_make":"Toyota", "vhc_model":"HiluxSR5" }, { "vhc_id":"001", "vhc_make":"Toyota", "vhc_model":"Landcruiser" }, { "vhc_id":"002", "vhc_make":"Ford", "vhc_model":"Ranger" }, { "vhc_id":"002", "vhc_make":"Ford", "vhc_model":"Falcon" ..

Read more

I’m using Laravel query builder and trying to do something like this: $builder = DB::table(‘mainTable’)->where("mainTable.id", "=", 111); $builder->leftJoinSub( DB::table(‘table2’) ->where(‘table2.id’, ‘=’, DB::raw("mainTable.subtableId")) ->where(‘table2.region’, ‘=’, DB::raw("mainTable.region")) ->orderBy(‘table2.someOrder’, ‘DESC’) ->select(‘kamery2.id’) ->first(), ‘myId’, ‘table2.id’, ‘=’, ‘myId’ ); And I’m getting The multi-part identifier "mainTable.subtableId" could not be bound. However when I write it with DB::raw(‘…’) it works. $builder->leftJoin( ..

Read more

I’m using Laravel query builder and I would like to ask how specify which row to use for the left join if many rows are returned? I have code like that: $builder->leftJoin(‘table2’, function (JoinClause $join) { $join->on(‘table2.id’, ‘=’, "table1.id") ->on(‘table2.region’, ‘=’, "table1.region") ->orderBy(‘table2.updatedAt’, ‘DESC’); // This is being ignored. }); The part $join->on()->on() returns multiple ..

Read more

I have a Laravel API call, and I want to paginate the result, but can’t seem to find the way that works. My call looks like this: public function index(Request $request){ $per_page = $request->input(‘per_page’); if(!$per_page) $per_page=20; $creditos = Prestacion::all(); $creditos = $creditos->paginate($per_page); return $this->collection($creditos, new PrestacionTransformer); } This returns the error: "Method IlluminateDatabaseEloquentCollection::paginate does not ..

Read more

I’m having difficulty converting this SQL query to Laravel Query Builder.I tried for hours but couldn’t get my head around this.I tried online tools for converting SQL to Query builder but didn’t work. Here is my code: SELECT technologies.name_en, Count(cig_members.id) AS CigTotal, Count(CASE WHEN cig_members.is_ethnic = 1 THEN 1 ELSE NULL END) AS CigTotalEthnic, Count(CASE ..

Read more

I am using laravel 8. I have this mysql command which I want to convert into laravel query builder style: select allocation.*,leav_leave_types.leave_type_code from (select * from leav_employee_annual_leave_allocations where leave_year_id=$year_id and employee_id=$user_id) as allocation left join leav_leave_types on (leav_leave_types.id=allocation.leave_type_id) Actually I want to first apply where clause then after I want to perform left join for ..

Read more

I have 5 different tables as projects, systems, attachments, tasks, users on Laravel I haven’t assign a relationship for each table what I am trying to do is linking each table using ID. $objFetch = Task::addSelect( [ ‘project_id’ => Project::select(‘title’)->whereColumn(‘project_id’, ‘projects.id’), ‘tester_id’ => User::select(‘name’)->whereColumn(‘tester_id’, ‘users.id’), ‘system_id’ => System::select(‘name’)->whereColumn(‘system_id’, ‘systems.id’), ‘assigned_id’ => User::select(‘name’)->whereColumn(‘assigned_id’, ‘users.id’), ], )->paginate(20); ..

Read more

how can you use the if else condition in the results of the query builder? $data = DB::table(‘peminjaman’) ->select(‘peminjaman.id as id_peminjaman’, ‘reads.pageread’, ‘books.*’, ‘pengarang.name as pengarang_name’ ) ->join(‘reads’,’reads.id_book’,’peminjaman.id_book’) ->join(‘books’,’books.id’,’=’,’reads.id_book’) ->join(‘pengarang’,’pengarang.id’,’=’,’books.idpengarang’) ->where(‘peminjaman.id_user’,$user->id) ->orderBy($sort,$urut) ->get(); i want to use some if conditioning like this, in real case there will be lots of other where, and the table ..

Read more