Category : query-builder

I have a problem using wherebetween to filter data by timestamp (datetime) To test query, this is my route Route::get(‘/test3/{iduser}/{waktu}’, ‘[email protected]_person’); and this is URL test http://localhost:8000/test3/32/2020-12-08 2010:15:00 this is my controller public function fgetxy_person($iduser, $waktu){ $time = Carbon::createFromFormat(‘Y-m-d H:i:s’, $waktu, ‘Asia/Jakarta’); // dd($time->addMinutes(-5)); //output: 10:10:00 // dd($time->addMinutes(30)); //output: 10:45:00 $xybeacon = DB::table(‘transaksi as tb’) ..

Read more

How to List all rows from a table (agendas) in my DB where records are saved by the connected user.I’m using default Auth from Laravel. public function index ($id = null) { $agendas = Agenda::where(‘id’, Auth::user()->id)->get(); $users = User::all(); return view(‘admin.agendas.index’, compact(‘agendas’,’users’,’id’)); } My controller here. Need help Sourc..

Read more

<?php namespace AppHttpControllers; use IlluminateHttpRequest; use IlluminateSupportFacadesDB; class universityController extends Controller { // public function getStudents(Request $req) { $students= DB::table(‘students’) ->join(‘applicants’, function ($join) { $join->on(‘students.id’, ‘=’, ‘applicants.studentid’) ->where(‘applicants.scholarshipid’,’=’, $req->scholarshipid); }) ->get(); return $students; // return $req->scholarshipid; } } Sourc..

Read more

I’m trying to do a query in database using temporary tables through WITH clause: WITH temp (id, category, brand, model, fullname) AS ( SELECT equipment.id, categories.name, equipment.brand, equipment.model, CONCAT(categories.name,’ ‘,equipment.brand,’ ‘,equipment.model) AS fullname FROM equipment INNER JOIN categories ON equipment.category_id = categories.id ) SELECT * FROM temp WHERE fullname LIKE ‘%VALUE%’ LIMIT 10 This was ..

Read more

Expected result: I’m trying to insert a key to includes same department data as below "data": { "Department A": { "code": "9781848574472", "department": "Department A", "total_quantity": 198, "total_price": "0.00" }, { "code": "9781848574649", "department": "Department A", "total_quantity": 8, "total_price": "0.00" }, "Department C": { "code": "ABC001", "department": "Department C", "total_quantity": 1, "total_price": "100.00" } }, ..

Read more

I have this Eloquent Query Builder in Laravel-5.8: $userCompany = Auth::user()->company_id; $userEmployee = Auth::user()->employee_id; $employeeCode = Auth::user()->employee_code; $employeeemptypeid = HrEmployee::where(’employee_code’, $employeeCode) ->where(‘company_id’, $userCompany) ->pluck(’employee_type_id’)->first(); $employeeegendercode = HrEmployee::where(’employee_code’, $employeeCode) ->where(‘company_id’, $userCompany) ->pluck(‘gender_code’)->first(); $leaveBalance = DB::table(‘hr_leave_types AS lt’) ->leftJoin(‘hr_leave_type_details AS ltd’, function($join) use ($userCompany) { $join->on(‘ltd.leave_type_id’, ‘=’, ‘lt.id’) ->where(‘ltd.company_id’, ‘=’, $userCompany) ->where(‘ltd.employee_type_id’, ‘=’, $employeeemptypeid); }) ->leftJoin(‘hr_leave_requests AS ..

Read more

I am write database query which fetch the record which does not have specific relation i am simply counting the number of records which doesn’t have relation . But it is taking too long . Here is my query Consignment::doesntHave(‘invoice_charges’)->count() But when I write query without relation it gives me quick result Consignment::count() What could ..

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

I have I need to insert multiple record in database . Currently I am inserting with loop which is causing timeout when record is big. Is there any way that we dont use loop? $consignments = Consignment::select(‘id’)->where(‘customer_id’,$invoice->customer_id)->doesntHave(‘invoice_charges’)->get(); foreach($consignments as $consignment){ InvoiceCharge::create([‘invoice_id’=>$invoice->id,’object_id’=>$consignment->id,’model’=>’Consignment’]); } consignment has hasOne relation in model public function invoice_charges() { return $this->hasOne(‘AppModelsAdminInvoiceCharge’, ‘object_id’)->where(‘model’, ..

Read more

I have three models. Section Subject Chapter Section has many subjects and subject has many chapters. I want to load a section with all its subjects and the subjects with all its chapters. Here is the code I am trying: $section = Section::find(1)->load([‘subjects’ => function ($query) { $query->load(‘chapters’); }]); This is not working. $query->load(‘chapters’) this ..

Read more

How to write this same code and get same data in eloquent relationship public function index(){ $data = DB::table(‘categories’) ->join(‘subcategories’, ‘categories.id’, ‘subcategories.cat_id’) ->select(‘categories.*’, ‘subcategories.*’)->paginate(5); return view (‘admin.subcategory.index’, compact(‘data’)); } Sourc..

Read more

I have 3 tables. transactions, packages and package_activities. Transaction hasMany Packages (using transactions_id => packages.transaction_id). Package has one Activity (using packages.tracking_number => package_activites.tracking_number) transactions: id packages: id | transaction_id | tracking_number package_activities: id | tracking_number | last_activity_at I am trying to join from transactions so that it transaction’s first package’s activities’s last_activity_at. If in Packages ..

Read more

I want to convert this SQL query to Query builder Laravel SELECT DISTINCT tb.id_alat AS idalat, ta.nama_alat, (SELECT SUM(tb_bbms.bbm) FROM tb_bbms WHERE tb_bbms.id_alat=idalat)AS total_bbm, (SELECT SUM(tb_bbms.jam_operasi) FROM tb_bbms WHERE tb_bbms.id_alat=idalat)AS jam_operasi, (SELECT SUM(tb_bbms.tarif) FROM tb_bbms WHERE tb_bbms.id_alat=idalat)AS tarif FROM tb_alat AS ta LEFT JOIN tb_bbms AS tb ON ta.id_alat = tb.id_alat AND Month(created_at)=10 Thans for ..

Read more