Laravel查询主键结果。

Question

我有问题与我的查询。我有4个用户，潜在客户，潜在客户，项目。所有这些都有关联

oneToMany relation喜欢用户有很多前景，潜在客户有很多潜在客户，潜在客户有很多项目。我的项目是我需要数据的表格。项目表如下所示。

$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(); 

我需要通过sales_phase得到forcast和group的总和。我需要2个结果。

1. sales_phase获取FORCAST组之和卫生组织lead_id是活动的（信息表有效= 1）

2.获取FORCAST组由sales_phase用于用户的总和。 （用户>潜在客户>潜在客户>项目）

对于第一个结果，我可以通过以下查询来制作数据透视表。但我怎么能通过参数在哪里条件，哪里铅活跃。并得到用户的结果;

$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(); 

请帮忙。我在sql中不够好。

Answer 1

尝试使用你的关系雄辩的方式。

问题1

$sum = Item::->with(['leads' => function($query) { 
     $query->where('lead', 'active') 
    }]) 
    ->groupBy('sales_phase') 
    ->selectRaw('SUM(forcast) AS sum, sales_phase') 
    ->pluck('sum', 'sales_phase'); 

问题2，对于用户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) AS sum, sales_phase') 
    }])->get(); 

一些相关Laravel文档：

• Querying relations
• groupBy
• Eager loading, and constraining eager loads