Laravel数据库查询返回错误的值

问题描述：

So in my database I have table called "records", which contains approximately 1k entries. This table contains such fields as "id", "title", "date_finished", "category_id", "user_id" and some more.

"date_finished" by default is 0000-00-00 00:00:00, but user can change it to current timestamp. And so, in this table approximately the half has default date_finished and another half has custom.

In my HTML I have input with type="month" which sends year and month to the controller in format like "2016-10".

And I need to get only those entries that have either default date_finished property or date_finished with month and year received from input.

In the beginning of my controller I wrote that

$record_query =  DB::table('records');

Then, I need some additional options to be used, so I have two arrays — $users and $categories containing some ids. Then, I do

$record_query = $record_query
       ->where(function($q) use ($users){ $q->whereIn('records.user_id', $users); })
       ->orWhere(function($q) use ($categories){                
            $q->whereIn('records.category_id', $categories);
        });

And after that I have about 700 entries left from 1k.

And here is the part where the problem is.

To get only those entries that meet the requirements mentioned above, I do

$month = Input::get('month');
$record_query = $record_query->where(function($w) use($month){
      $w->where('records.date_finished','0000-00-00 00:00:00')
      ->orWhere(function($q) use ($month){                
            $q->where('records.date_finished','!=','0000-00-00 00:00:00')
           ->whereBetween('records.date_finished',array(date('Y-m-d', strtotime($month)), date('Y-m-d', strtotime($month.'+1 month'))));
       });
});

And after this modification I get some 300 entries that do not meet those requirements. The array I get the end contains some random entries with default date_finished and custom date_finished that has nothing common with one I send from input.

When I run the same query directly in SQL, I get correct results.

Where am I wrong?

答

I think you need something like

$date = Carbon::createFromDate(null, $month, null);

$record_query = $record_query->where(function($w) use($date){
    $w->where('records.date_finished','0000-00-00 00:00:00')
        ->orWhere(function($q) use ($date){                
            $q->whereBetween('records.date_finished', [$date->toDateString(), $date->addMonth()->toDateString()])));
   });
});

Laravel数据库查询返回错误的值

相关推荐