Query Builder: how to retrieve records for every date in range, even if data is not there for some dates

If I have table Table with the following structure:

Schema::create('Table', function (Blueprint $table) {
            $table->increments('id');
            $table->date('date');
            $table->integer('sales')->default(0);

I am able to query it and retrieve data for a range of dates like this:

    // assume $startDate and $endDate exist and are valid
        $data = DB::table('Table')
        ->select(DB::raw('Table.sales as sales'))
        ->whereBetween('Table.date', [$startDate, $endDate])

But what I would really like is to get a record for every date in the date range, even if there is no matching entry for some dates (sales can be 0, or null in that case).

Does anyone have any suggestions on the best way to go about this? I've thought about making a static table of dates and using it as the left side of a join. It seems like a common pattern though, so I'm curious to know what others have tried, or if there's an obvious solution I've missed.

Thank you for your time.

robrogers3

8 years ago

Level 37

$data = DB::table('Table') ->select(DB::raw('Table.sales as sales')) ->whereBetween('Table.date', [$startDate, $endDate]) ->orWhereNull('Table.date')

this said the date column is not nullable. so there will always be a date.

am I missing somethnig?

1 like

eloperdev

8 years ago

Level 1

Thanks for the response @robrogers3!

I probably didn't explain it too clearly, I don't think that's quite what I need. I'll give a better illustration

So, say there is data like this:

id, date, sales
================
1, 2017-11-01, 5000
2, 2017-11-03, 6000

And I want to retrieve sales for the date range 2017-11-01 - 2017-11-03 (it will actually be a month at a time I ultimately want to get, but this makes it simpler for now)

I'd like the result to be (I realize I need to somehow select all the dates here too)

date, sales
2017-11-01, 5000
2017-11-02, 0
2017-11-03, 6000

RamjithAp

8 years ago

Best Answer

Level 10

First, convert your date range into array of all dates comes in between like this

 $period = new DatePeriod(new DateTime('2017-11-01'), new DateInterval('P1D'), new DateTime('2017-11-03 +1 day'));
    foreach ($period as $date) {
        $dates[] = $date->format("Y-m-d");
    }
  var_dump($dates);
  //output will be {"2017-11-01","2017-11-02","2017-11-03"}

Now use foreach loop and grab the results then form your output like this

$result = [];
foreach($dates as $date){
       $sales= DB::table('Table')->whereDate('date',$date)->pluck('sales');
       if($sales){
       $result[] = array($date =>$sales);
       }else {
       $result[] = array($date =>0);
       }
        
}
return $result;

2 likes

angelothegreat13

4 years ago

Level 1

@RamjithAp do you have a faster solution in this problem ? ty

rahulwalgude

4 years ago

Level 1

@RamjithAp Thanks... I am looking for this since last 3-4 days and finally get the Ans. Just one query, in array its showing "Null" where i want "0". How can i get the same.. I am new to coding . This is my output 0 => array:1 [▼ "2022-02-09" => Illuminate\Support\Collection {#348 ▼ #items: array:1 [▼ 0 => null ] } and i am expecting "2022-02-09" = "0"

eloperdev

8 years ago

Level 1

Thanks @RamjithAp. I understand your solution and it looks like I will go with something very close to that.

I was hoping that there might be a fancy way to do this all in a query, but I haven't had much success getting that to work so far. So the simple solution is probably the way to go.

Please or to participate in this conversation.