Category : laravel-query-builder

i have sql query which works as expected in raw sql but not in query builder where a.price = ‘399’ and b.seq=(select max(seq) from sells_2020 where pa_no=a.pa_no) order by a.pa_no this works fine in sql but not in Query builder i try this , ->where(‘a.price’,399) ->where(‘b.seq’,’=’,DB::raw(‘select max(seq) from sells_2020 where pa_no=a.pa_no’)) ->OrderBy(‘a.pa_no’) So how can ..

Read more

select * FROM (SELECT o.customerID,o.slot,o.Mobile,o.orderNumber as orderNumber,o.Name, o.acknowledge_status,o.customerAddress, o.srType,s.startTime,s.endTime,DATE_FORMAT(r.roasterDate, ‘%d-%m-%Y’)AS roasteDate,tlo.name as oltname,o.portId,o.slaClock ,dop.podName,st.statusFFA as srStatus from order o LEFT JOIN slot s ON s.slotId=o.slotId Left join roast r ON o.roasterId=r.roasterId LEFT JOIN engins e ON o.pengId=e.userId LEFT JOIN tlo ON tlo.deviceId=o.deviceId LEFT JOIN dop on dop.podCode=tlo.podCode LEFT JOIN transition_status st ON st.statusReasonCRM=o.srStatus AND o.acknowledge_status=st.orderTransition ..

Read more

select * FROM (SELECT o.customerID,o.slot,o.Mobile,o.orderNumber as orderNumber,o.Name, o.acknowledge_status,o.customerAddress, o.srType,s.startTime,s.endTime,DATE_FORMAT(r.roasterDate, ‘%d-%m-%Y’)AS roasteDate,tlo.name as oltname,o.portId,o.slaClock ,dop.podName,st.statusFFA as srStatus from order o LEFT JOIN slot s ON s.slotId=o.slotId Left join roast r ON o.roasterId=r.roasterId LEFT JOIN engins e ON o.pengId=e.userId LEFT JOIN tlo ON tlo.deviceId=o.deviceId LEFT JOIN dop on dop.podCode=tlo.podCode LEFT JOIN transition_status st ON st.statusReasonCRM=o.srStatus AND o.acknowledge_status=st.orderTransition ..

Read more

Nose que titulo colocarle a mi duda, sucede lo siguiente, tengo la siguiente consulta en mi controlador: $data = DB::table(‘payments’) ->leftJoin(‘users as u’, ‘payments.user_id’, ‘=’, ‘u.id’ ) ->leftJoin(‘role_user’, ‘role_user.user_id’, ‘=’, ‘u.id’ ) ->leftJoin(‘roles’, ‘role_user.role_id’, ‘=’, ‘roles.id’ ) ->where(‘payments.id’ , ‘=’, $payment->id) ->select(‘u.name’,’payments.vouche’, ‘roles.name’) ->get(); dd($data); Cuando intento traer en mi consulta los campos de las ..

Read more

Using Laravel 8 I have 3 models (Album, Track, Playcount). One Album has many Tracks, and one track has many Playcounts (every 24 hours a job will run and update the playcount). Album Model public function playcounts() { return $this->hasManyThrough(‘AppModelsPlaycount’, ‘AppModelsTrack’); } Track Model public function playcounts() { return $this->hasMany(Playcount::class, ‘track_id’); } Playcount Model public ..

Read more

I am trying to get nearby companies here is my query $lat = 21.41241750000001; $long = 39.23094140625001; return $this->model->select(‘id’, ‘lat’, ‘long’, DB::raw(sprintf( ‘(6371 * acos(cos(radians(%1$.7f)) * cos(radians(`lat`)) * cos(radians(`long`) – radians(%2$.7f)) + sin(radians(%1$.7f)) * sin(radians(`lat`)))) AS `distance`’, $lat, $long ))) ->having(‘distance’, ‘<‘, 50) ->orderBy(‘distance’, ‘asc’) ->paginate(); and always get me SQLSTATE[42S22]: Column not found: 1054 ..

Read more

I have a very hard situation and I been trying to find a solution either with query builder or eloquent but had no luck for days. I have a database table/eloquent model "Attendance" with the following fields: id, employee_id, timesheet_id, attendance_time ‘datetime’ this table represents fingerprint log for every employee in particular branch. timesheet_id: represents ..

Read more

Please I want to count the number of Items in each grouped collection. assuming the collection returns three group Items, I want to count the collection in each group I did this but it isn’t working out for me $collection = Vendor::all(); $grouped = $collection->groupBy(‘vendor_discount’); $counted = $grouped->countBy(); return $counted -> all(); this is the ..

Read more

Im trying to get laravel 7x to work with impala/kudu provided by cloudera. Ive got the ODBC connection which connects fine and if you run raw sql query it also works fine. Database connection entry: ‘impala_connect’ => [ ‘driver’ => ‘sqlsrv’, ‘odbc’ => true, ‘odbc_datasource_name’=> ‘cloudera_odbc_connection’, ‘host’ => ”, ‘port’ => ”, ‘database’ => ”, ..

Read more

I am learning laravel and although i have found a solution, i am curious if there is a "better" or more "laravel" way to do it. Here is the working solution i have: (example 1) $students = Subclass::query() ->join(‘student_subclass’, ‘subclasses.id’, ‘=’, ‘student_subclass.subclass_id’) ->join(‘students’, ‘students.id’, ‘=’, ‘student_subclass.student_id’) ->select(‘*’) ->where(‘subclasses.id’, $request->subclass_id) ->get(); I have 3 tables: subclasses, ..

Read more

I am new in Laravel! I have this query and it works well, I can get the information from the database: $users = DB::select("SELECT q.user_id, q.created_at, SUM(winner) as total FROM ("SELECT tb1.user_id, tb1.winner, created_at, ROW_NUMBER() OVER(PARTITION BY tb1.user_id ORDER BY created_at DESC) rank FROM tournament_battles as tb1 WHERE created_at >= CURDATE() ") q WHERE q.rank ..

Read more

Below is my query; select "column1", "desc", if r_id = P then Y else ‘N’ endif As is_selected from "other" where "rtype" = D_STATUS Below is my query builder function, DB::table(‘other’) ->select(‘r_id’, ‘description’, DB::raw("if r_id =’P’ then ‘Y’ else ‘N’ endif As is_selected")) //Error ->where(‘rtype’, ‘=’, $strType ) ->get(); Here I got error ; Column ..

Read more

I have a query like this, to make count from my query, I want finding where id is with the minimum count, with result id in an array $id = Departemen::where(‘open’, 1)->where(‘name’, ‘LIKE’, ‘%LOKET%’)->pluck(‘id’)- >toArray(); $matchThese = [‘called’ => 0, ‘onserved’ => 0]; $smaller = Queues::select(‘department_id’,DB::raw(‘count(*) as total’)) ->where($matchThese)->whereIn(‘department_id’, $id)->groupBy(‘department_id’)->get(); Sourc..

Read more

I’m trying to filter users based on withCount() result and with() relationship. By filter, I mean do not return that user at all. User::with(‘info’) ->withCount([‘transactions as balance’ => function($query) { $query->select(DB::raw(‘sum(credit_movement)’)); }]); This works for returning the "info" relationship and "balance" column for user’s balance. Now I want to query based on 2 things. where(‘balance’, ..

Read more

I read the Laravel documentation about the query builder, however I can not figure this out: Current situation: My User Model has many upperCategories and my upperCategory Model has many subCategories. That is why I can do this: auth()->user()->upperCategories()->with(‘subCategories’)->get()->toArray(); Note that I am using eager loading for improved performance. This code is working fine. It ..

Read more

Here is my logic. pages table: id, name, created_at, updated_at: types table: id, name, created_at, updated_at: spots table: id, name, page_id, type_id, created_at, updated_at $pages = Page::latest()->get(); $types = Type::latest()->get(); $spots = Spot::latest()->get(); What I need to display on view is as following: @foreach(`pages` x `types` as $row) $row->page_name, $row->type_name, $row->spots_count() @endforeach How can I ..

Read more

I have these structure tables: writers id – name – governorate_id – show_in_governorate(true, false) posts id – title – writer_id users id – name – governorate_id my code: $posts = Post::where(‘active’,true) ->whereHas(‘wirter’,function($query){ $query->where(‘show_in_governorate’,true) ->where(‘governorate_id’,auth()->user()->governorate_id); }) ->get(); I need to say if any writer make show_in_governorate (true) just show his posts to users with the same ..

Read more