laravel - Laravel 查询生成器中使用 group by 聚合查询

Question

我有一只 table 鸟。

我想在一个查询中计算每种鸟类的数量。

如果可能的话，我想将这些查询组合成 Eloquent 中的单个查询。

 select count(id) as count1 from birds where kind = a;

 select count(id) as count2 from birds where kind = b;

 select count(id) as count2 from birds where kind = c;

我尝试过类似的事情

$first = DB::table('birds')->selectRaw('count(id) as count1')->where('kind','a');
DB::table('birds')->selectRaw('count(id) as count2')->where('kind','b')->unionAll($first)->get();

我认为工会没有给我我想要的东西。

我只需要类似的东西

DB::raw(' (select count(id) from birds where kind = a) as count1 ', ' (select count(id)  from  birds where kind = a) as count2  ', ' (select count(id) from birds where kind = a) as count3 ')

我想合并查询

喜欢

Select ( select count(id)  from birds where kind = 'a') as count1, ( select count(id)  from birds where kind = 'b') as count2,  ( select count(id)  from birds where kind ='c') as count3 from birds ;

。请告诉我如何实现它。

Answer 1

特别学习 SQL、group by 和 aggregates。

这就是 Laravel 中您所需要的:

DB::table('birds')
  ->selectRaw('count(id) as count, kind')
  ->groupBy('kind')
  ->lists('count', 'kind');
  // or get()

lists 将返回如下所示的数组:

array(
  'kind_1' => '15',
  'kind_2' => '10',
  ...
);

get 将返回一个 stdObjects 数组，因此可能不是您想要的:

array(
  0 => StdObject {
    'kind' => 'kind_1',
    'count' => '15'
  },
  1 => StdObject {
    'kind' => 'kind_2',
    'count' => '10'
  },
  ...
);

如果您只想获取特定种类的鸟类，请使用whereIn:

DB::table('birds')
  ->selectRaw('count(id) as count, kind')
  ->groupBy('kind')
  ->whereIn('kind', ['kind_1', 'kind_2', 'kind_3'])
  ->lists('count', 'kind');