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 ..

Read more

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) { ..

Read more

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 ..

Read more

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 ..

Read more

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’); }) ..

Read more

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 ..

Read more

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 ..

Read more

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) { ..

Read more

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 ..

Read more

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 ..

Read more

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 ..

Read more

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 ..

Read more

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 ..

Read more

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 ..

Read more