Laravel database query returns wrong values

Question

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?

Suggestion: when mysql shows a datetime like 0000-00-00 00:00:00 it means that it is NULL so try using WHERE date_finished IS NOT NULL or maybe use IS NULL depending on what you want — RiggsFolly
– RiggsFolly, Commented Oct 19, 2016 at 14:55

aleksejjj · Accepted Answer · 2016-10-19 15:27:01Z

1

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()])));
   });
});

answered Oct 19, 2016 at 15:27

aleksejjj

1,83512 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel database query returns wrong values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related