I have a query join 3 tables for sales with about 15000 rows. The query takes about 22 seconds to respond, this is too big for me. Someone, please help me optimize this query. I use Laravel and use the filter from the form. Here is my query : $data = DB::table(‘crm_donhang as dh’) ->select(‘dh.id ..
Category : laravel-query-builder
I have 3 model 1.SellInvoice Model: protected $table = "sell_invoices"; public function jewelsItems(){ return $this->hasMany(JewelsItem::class,’buy_invoice_id’); } 2.Jewel model: public function jewelsItems(){ return $this->hasMany(‘AppModelsJewelsItem’); } 3.sellInvoice model: protected $table = "sell_invoices"; public function jewelsItems(){ return $this->hasMany(JewelsItem::class,’buy_invoice_id’); } Query: find 10 number of most sale jewel in my db or sort result on number of sales from ..
I want to union table questions and choices in query builder JSON Resource laravel, when I’m trying this, I’m getting an error, what’s wrong with my code?, please give me some advice on this code This is my CurriculumDisplayResource <?php namespace AppHttpResources; use IlluminateHttpResourcesJsonJsonResource; use IlluminateSupportFacadesDB; class CurriculumDisplayResource extends JsonResource { public function toArray($request) { ..
This query works with group by $data = DB::select(DB::raw("SELECT SUM(pending) as total , datediff(NOW(), created_at) as maturity FROM `admin_case_info` WHERE deleted_at is NULL group by maturity ")); But when I joined 2 tables. $data = DB::select( DB::raw("SELECT admin_case_info.case_type, admin_case_info.case_status, SUM(admin_case_info.pending) as total, datediff(NOW(), admin_case_info.created_at) as maturity FROM admin_case_info JOIN users_mws on users_mws.id = admin_case_info.users_mws_id JOIN ..
I have BelnogstoMany relation in my model which I defined like this class MailUsers extends Model { public function group () { return $this->belongsToMany(‘AppModelsGroup’, ‘user_groups’,’user_id’,’group_id’); } } and in the other model, I defined the relationship like this class Group extends Model { public function customer () { return $this->belongsToMany(‘AppModelsMailUsers’, ‘user_groups’,’group_id’,’user_id’); } } when I ..
I have 32000 occurrence of transactions in my db, this query takes too long about 10 sec or more, how can i optimize please $data = DB::table(‘transactions’) ->when($merchantId, function($query) use($merchantId) { return $query->where(‘user_id’, $merchantId); }) ->whereBetween(‘created_at’, [ Carbon::now()->startOfYear(), Carbon::now()->endOfYear(), ])->when($app, function($query) use($app) { return $query->where(‘application_id’, $app); }) ->where(function ($query) { $query->where(‘status’, ‘CANCELLED’) ->orWhere(‘status’, ‘FAILED’); }) ..
I am updating multiple rows in laravel . StoragePeriod::where(‘customer_id’, $invoice->customer_id)->update([‘invoice_id’ => $invoice->id]); How I can get ids of those updated records? Sourc..
I have two IlluminateDatabaseQueryBuilder or IlluminateDatabaseEloquentBuilder objects, each for the same table/model. I need to merge these two objects into a single one so that the resultant Builder has all the wheres, joins, group by’s, etc of both the objects As an example, suppose $query1 = MyModel::where(‘column_name’, $value); $query2 = MyModel::where(‘some_other_column_name’, $someOtherValue)->join(…some conditions); I need ..
projectmember table has a column name status. what i want is to update the status to 6 if $req value does’nt have on projectmember.member_id filed following code update all the status to 40 of a specified project_id $req = [1,2,3]; foreach ($req as $key => $item) { $updated = Projectmember::where(‘member_id’,’!=’, $item ) -> where(‘project_id’,’=’, $id ..
I am using laravel to make a query and return all results that match several criteria. One of them is by date, the problem is that I store dates in the database in this format: 2021-03-26 12:20:00 and display them in this format: March 26, 2021 It is a requirement for the project that if ..
id incharge_id member_id project_id 1 3 1 1 2 3 4 1 3 3 2 1 4 3 6 1 5 5 2 2 6 5 4 2 I am working with update on above table projectmembers. $request->member_id returns an array that contains few user_ids like [1,4,2,6] I have to update the member_id’s of specific ..
I want to write one raw query for four tables . Currently I am doing separately with laravel eloquent. But data increasing rapidly and one day this query will fail or take long time . What I am doing is getting customer ids from all those tables. which meets those requirements. Then I am using ..
I have a job and inside this job I take 2 record from the database and afterwards I call that job again. Each time the job gets called it takes records with limit of two. But the problem is this query skip 2 records each time. I mean it will get two records from database ..
Here is the relevant part of the ERD: Post is an ad of a computer or laptop. A computer or laptop contains several parts (processor, memory, storage etc). A Postpart represents one such part. A component is particular model of hardware, e.g. Intel Core i9 (9th gen) or Samsung 970 Evo Plus. Since each component ..
I have data from a table structured like so: table: students { id: name: classes: [ { name: "gym" }, { name: "science" }, { name: "math" }, { name: "english" } ] } reference array: science, math How do I return only records that match the values found in the reference array. For example, ..
I’m working on laravel with join. I have tables that need to join it’s working fine as defined. But I want to change two tables should work as before but it should get all data of the third table without any condition. Below Are the code and explanations of what I want to do. $reports ..
How do i query to make total count per day based on a name or an id? id name 1 Facebook 2 Twitter 3 Reddit id page_id social_id visited_at 1 1 1 2021-03-27 2 1 1 2021-03-27 3 1 2 2021-03-27 4 1 2 2021-03-27 5 1 3 2021-03-27 6 1 3 2021-03-27 7 1 ..
I want to calculate the sum of all agent product sales per lead. Relations : User : public function leads(): belongsToMany { return $this->belongsToMany( Lead::class, DispatchedLead::TABLE, DispatchedLead::AGENT_ID_COLUMN_NAME, DispatchedLead::LEAD_ID_COLUMN_NAME ) ->withTimestamps(); } Product : public function products(): BelongsToMany { return $this->belongsToMany(Product::class, LeadProduct::TABLE) ->withPivot(LeadProduct::QUANTITY_COLUMN_NAME, LeadProduct::PRICE_COLUMN_NAME,LeadProduct::STOCK_SKU_COLUMN_NAME) ->withTimestamps(); } My query select : User::with([ “leads.products” => function ($query) { ..
I have a MYSQL query which is a little complicated, I have tried several times and searched the same query case as me on the internet, I tried it but the results were not as expected. So I decided to look for a way to use Manual Query in Laravel 8 which has the same ..
I am trying to use laravel query builder like:- $users = DB::table(‘baid_collection’) ->select(DB::raw(‘sum(total) as total_collect,collection_limit_c’)) ->join(‘users_cstm’, ‘baid_collections.assigned_user_id’, ‘=’, ‘users_cstm.id_c’) ->where(DB::raw("assigned_user_id = ‘$userId’ and DATE(date_entered)=CURDATE()")) ->groupBy(‘assigned_user_id’) ->get(); This query should be like select sum(total) as total_collect,collection_limit_c from `baid_collections` inner join `users_cstm` on `baid_collections`.`assigned_user_id` = `users_cstm`.`id_c` where assigned_user_id = ‘15426608-3ea5-f299-7a80-601bd06be2d9’ and DATE(date_entered)=CURDATE() group by `assigned_user_id` But last ..
So, I want to add a column (‘Semester_TA’) and change the property of this column (‘kode_mk’) to be unique value, so I created this migration: public function up() { Schema::table(‘mk’, function(Blueprint $table){ $table->string(‘Semester_TA’)->after(‘sks’); $table->string(‘kode_mk’)->unique(); }); Schema::table(‘lr2’, function(Blueprint $table){ $table->dropForeign([‘id’]); }); } public function down() { // } I also want to drop/delete this foreign key ..
I have a raw query that I want to convert into querybuilder since I need to modify it. Here is the old query SELECT mws_name, MAX(created_at) as created_at, updated_at, SUM(pending) as pending, SUM(approved) as approved, SUM(rejected) as rejected FROM ( SELECT um.mws_name, ac.users_mws_id, ac.case_type, ac.created_at, ac.pending, ac.approved, ac.rejected, ac.updated_at FROM admin_case_info ac JOIN users_mws um ..
I’m trying to use laravel eloquent to process a query but it doesn’t recognize the relationship $query = $site ->consumers() ->with([ ‘transactions’ => function($_query) use ($thresh) { $_query ->where(‘status’, ‘processed’) ->where(‘approved’, 1) ->selectRaw("SUM({$thresh} – value + bonus_value) AS total_points") ; } ]) ->where(‘total_points’, ‘<=’, 5000) ; dd($query->get()->toArray()); I have this code. When I add selectRaw ..
I’m building an application on Laravel 8.0 where I’m trying to fetch relation which doesn’t exists in joins with DB statement. I’m not getting the desired result I tried using public function ProductList() { $innerQuery = DB::table(‘projects as p’) ->join(‘construction_product_projects as cpp’, ‘p.id’, ‘=’, ‘cpp.project_id’) ->join(‘construction_product as cp’, ‘cpp.construction_product_id’, ‘=’, ‘cp.id’) ->where(‘cp.status’, ‘saved’) ->join(‘project_associate_brand as ..
I am making site with laravel and vuejs. I use complex Eloquent query for getting data from DB as follows. $query->with([ ‘deviceAssignment.deviceSetting.sim’, ‘deviceAssignment.deviceSetting.device.deviceType’, ‘deviceSignal’, ‘deviceAssignment.deviceSetting.deviceGroup’, ‘deviceAssignment.deviceSetting.company’, ‘deviceAssignment.deviceSetting.phones’]) ->orderBy(‘created_at’, ‘desc’) ->get(); I use several tables at once, then this size of data is somewhat huge, so I want to paginate this data. I placed paginator in ..
Web Development Company
Free Hosting Company
