查询计数也是不匹配的值

h79rfbju  于 2021-06-25  发布在  Mysql
关注(0)|答案(2)|浏览(341)

我有一个返回匹配值的查询。

Availability::select(DB::raw('count(start) as count,start'))
                                    ->whereIn('start', [100,200,300,400])
                                    ->groupBy('start')
                                    ->get();

并返回:

count | start
1     | 100
2     | 200

我希望在结果中显示0个计数值。比如:

count | start
1     | 100
2     | 200
0     | 300
0     | 400

dd(),日期为值。['2018-09-10', '2018-09-18', '2018-09-16','2018-09-15'].

array:1 [▼
  0 => array:2 [▼
    "count" => 1
    "start" => "2018-09-10"
  ]
]

有什么想法吗?提前感谢:)

b5lpy0ml

b5lpy0ml1#

您可以用需要比较的变量(100200300400)创建一个额外的表。然后你可以一起数数。
这并不漂亮,但这里有一个例子:http://sqlfiddle.com/#!9/3f906c/1号

kqlmhetl

kqlmhetl2#

因此,使用集合可以执行以下操作:
在数组/集合中定义开始日期

$dates = collect(['2018-09-10', '2018-09-18', '2018-09-16', '2018-09-15']);

你可以用这个 whereIn('start', $dates) ```
$result = Availability::...
$collection = $dates->map(function($item) use($result) {
return ['count' => $result->firstWhere('start', $item)['count'] ?? 0, // in case line does not work use optional($result->firstWhere('start', $item))->count ?? 0,
'start' => $item];
});

dd($collection); // your desired result

我和tinker一起玩,所以这里是最后的tinker课程:

$a = collect([['count' => 1, 'start' => '2018-09-10'], ['count' => 3, 'start' => '2018-09-11']])
=> Illuminate\Support\Collection {#828
all: [
[
"count" => 1,
"start" => "2018-09-10",
],
[
"count" => 3,
"start" => "2018-09-11",
],
],
}
$b = collect(['2018-09-10', '2018-09-11', '2018-10-14'])
=> Illuminate\Support\Collection {#809
all: [
"2018-09-10",
"2018-09-11",
"2018-10-14",
],
}
$b->map(function($item) use($a) {return ['count' => $a->firstWhere('start', $item)['count'] ?? 0, 'start' => $item];});
=> Illuminate\Support\Collection {#832
all: [
[
"count" => 1,
"start" => "2018-09-10",
],
[
"count" => 3,
"start" => "2018-09-11",
],
[
"count" => 0,
"start" => "2018-10-14",
],
],
}

相关问题