Query count also non matching values

Question

I have this query which returns matching values.

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

And returns:

count | start
1     | 100
2     | 200

I want the show in the result also 0 count values. Like:

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

dd() with dates as values. ['2018-09-10', '2018-09-18', '2018-09-16','2018-09-15'].

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

Any ideas? Thanks in advance :)

Oof, you'd have to include an outer join to select the counts with an amount of zero. Not exactly a query builder expert, so maybe this post can serve as inspiration: https://stackoverflow.com/questions/14793057/how-to-include-zero-0-results-in-count-aggregate — Loek, May 08 '18 at 11:52
@Kyslik The query is a bit more complicated but for simplicity i edited a bit with other values and shorter. You think i can use collection to show also non matching values? — Antonio Gocaj, May 08 '18 at 12:28
Yea exactly I would go the collection way instead of sql shenanigans :), so show up the `dd()` and I will try. — Kyslik, May 08 '18 at 12:29

Kyslik · Accepted Answer · 2018-05-08T13:09:31.107

So using collections you can do following:

define your start dates in array / collection

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

You can use this in 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

I played with tinker so here is the final tinker session:

>>> $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",
       ],
     ],
   }
>>>

score 0 · Answer 2 · answered May 08 '18 at 12:21

0

You could create an extra table with the variables you need to compare to (100, 200, 300, 400). Then you can join and count.

It isn't pretty, but here you got an example: http://sqlfiddle.com/#!9/3f906c/1

answered May 08 '18 at 12:21

Ron Nabuurs

1,528
11
29

Hmm, i don't think it will work in my case because the values in the array are dynamic. They change every time. Your suggestion works fine though for constant values. Thanks anyway :) – Antonio Gocaj May 08 '18 at 12:34
Aa alright, well then its not gonna work indeed. No problem for the suggestion anyway. Goodluck with finding some dynamic solution :D – Ron Nabuurs May 08 '18 at 14:06

Query count also non matching values

2 Answers2