Support the ongoing development of Laravel.io →
Laravel Eloquent Database

Hi, I am having problem with my query. I have 4 tables users,prospects,leads,items. All of them related oneToMany relation Like users hasMany prospects, prospects hasMany Leads, leads hasMany Items. My Items is the table that I need the data from. Item table is like below.

 $table->increments('id');
            $table->integer('lead_id');
            $table->string('sales_phase',11);  // the sale_phase has 4 category only 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 to get the the sum of forcast and group by sales_phase. I need 2 result.

  1. Get the sum of forcast group by sales_phase and whos lead_id is active(Leads table active=1)
  2. Get the sum of forcast group by sales_phase for a user. (user>prospect>lead>item)

For the first result I am able to make the the pivot table by this below query. But how I can pass parameter in where condition, where lead is active. And get result by user;

 $data = DB::table('items')
            ->select(DB::raw("lead_id,
                SUM(CASE WHEN sales_phase='New Lead' THEN forcast ELSE 0 END) AS NEW_LEAD, SUM(CASE 	WHEN sales_phase='Contact' THEN forcast ELSE 0 END) AS CONTACT, SUM(CASE WHEN 	sales_phase='Quotation' THEN forcast ELSE 0 END) AS QUOTATION, SUM(CASE WHEN 	sales_phase='Order' THEN forcast ELSE 0 END) AS ORDERS
                "))
                ->groupBy('lead_id')
                ->get();

Please help. Im not good enough in sql.

Last updated 2 years ago.
0

Sign in to participate in this thread!

Eventy

Your banner here too?

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2025 Laravel.io - All rights reserved.