Laravel query pivot result.

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.

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/2uHnPBu
via IFTTT