Laravel Eloquent whereIn，从数据库中检索记录，包括缺少日期

I am new to Laravel.

I am building app that counts your daily steps.
And I am building API, so I need to return 0 steps when there are no results in DB for selected days in array.

But if there is data for some of the days to return actual steps and to return 0 for other days. I have tried this:

$steps = Step::whereIn('date', $dates)
             ->where('user_id', Auth::id())
             ->get();

But it returns only matches. As I wrote I want to return data for all days, not only for days in DB. Please, help :)

This will never be possible with a simple Query as the DB can not give you anything, it does not know about. You can solve this either progrmmatically bei iterating over the result and adding all missing dates with 0-value or by adding a table to your DB which contains all dates and then left join your steps table with it in the query.

Actually you must have a table for all dates that you list. Because you can only get dates if the records exists in the table. Another option is making a loop in PHP for all dates but this creates lots of query that is not optimal solution.

If you have a dates table, you can join it with your steps table and may use COALESCE command. I recommend you to write the query as pure SQL and then convert it to Eloquent query.

If You don't want to add empty record to database for missing dates

it would be headache like this:

$dates = [
  '2016-12-01',
  '2016-12-02',
  ...
  '2016-12-31'
];
$records = Step::whereIn('date', $dates)
               ->where('user_id', Auth::id())
               ->get();

$steps = [];
$dates = array_flip($dates);      // flipping array like: ['2016-12-01' => 0, '2016-12-02' => 1, ...]
foreach($records AS $record) {    // iterating records
  $steps[] = $record;             // collecting records
  unset($dates[$record->date]);   // removing date that has record for that day
}

$dates = array_flip($dates);      // flipping back: [0 => '2016-12-01', ...]
foreach($dates as $date) {        // iterating dates that do not have data in db and creating model objects from them
  // creating missing record "on the fly"
  $Step = new Step([
    'date' => $date, 
    'user_id' => Auth::id(), 
    'steps' => 0
  ]);
  $Step->save();
  $steps[] = $Step; // adding model instance to resulting array
}

usort($steps, function($a, $b) { // sorting array of steps by date
  if(strtotime($a->date) > strtotime($b->date)) return 1;
  if(strtotime($a->date) < strtotime($b->date)) return -1;
  return 0;
});

so my recommendation is to have some console command (/app/Console/Commands/) that will do processing every night and make sure that all records are consistent.

I mean to create some background batch process that will "close the day" and create some records in database if user has no record for that date.

this recommendation simplifies everything, so Your controller will just get data as usual without any additional calculations, iterations and etc.