mysql - Laravel query pivot result. -
i having problem query. have 4 tables users,prospects,leads,items. of them related
onetomany relation users hasmany prospects, prospects hasmany leads, leads hasmany items. items table need data from. item table below.
$table->increments('id'); $table->integer('lead_id'); $table->string('sales_phase',11); // sale_phase has 4 category a,b,c,d $table->string('product_name',32); $table->decimal('price',8,2)->default(0.00); $table->integer('qty'); $table->integer('probability')->default(0); $table->decimal('forcast',8,2)->default(0.00); $table->string('note')->nullable(); $table->timestamps(); i need the sum of forcast , group sales_phase. need 2 result.
1. sum of forcast group sales_phase , whos lead_id active(leads table active=1)
2. sum of forcast group sales_phase user. (user>prospect>lead>item)
for first result able make the pivot table below query. how can pass parameter in condition, lead active. , result user;
$data = db::table('items') ->select(db::raw("lead_id, sum(case when sales_phase='new lead' forcast else 0 end) new_lead, sum(case when sales_phase='contact' forcast else 0 end) contact, sum(case when sales_phase='quotation' forcast else 0 end) quotation, sum(case when sales_phase='order' forcast else 0 end) orders ")) ->groupby('lead_id') ->get(); please help. im not enough in sql.
try eloquent way, using relations.
question 1
$sum = item::->with(['leads' => function($query) { $query->where('lead', 'active') }]) ->groupby('sales_phase') ->selectraw('sum(forcast) sum, sales_phase') ->pluck('sum', 'sales_phase'); question 2, user id x
$usersum = user::where('id', x) ->with(['prospects.leads' => function($query) { $query->where('lead', 'active') }]) ->with(['prospects.leads.items' => function($query) { $query->groupby('sales_phase') ->selectraw('sum(forcast) sum, sales_phase') }])->get(); some relevant laravel docs:
Comments
Post a Comment