Crni Crni - 2 months ago 18
MySQL Question

Eloquent multiple where from date fields <= >=

I want to select Items from mySql table.

I use Laravel 5.2 & Eloquent

My table structure:

id | item_id | name | date_start | date_end


  • id = primary, auto

  • item_id = int

  • name = text

  • date_start & date_end is 'Date' Type and have following format '2016-09-14'



my query:

$dateFormated = date('Y-m-d',$tempDate);
$items = ItemList::where('item_id', $id)
->where('date_start', '>=', $dateFormated)
->where('date_end', '<=', $dateFormated)
->get()


If I remove one of my where clauses I get correct results for all Items with the right date_start/date_end value.

what am I doing wrong, what can I do?

Answer

You just need to swap the operators and you'll be good to go

  ->where('date_start', '<=', $dateFormated)
                         ^
  ->where('date_end',   '>=', $dateFormated)
                         ^

According to the data provided in comments, date_start must fall on or before the formattedDate and date_end after, hence your conditions were opposite of what they should have been.


Old Answer

  ->where('date_start', '>=', $dateFormated)
  ->where('date_end', '<=', $dateFormated)

How can a start date and end date be compared to the same date unless you want to look for records for only 1 specific date?

Syntax is fine but logic might not be. Such a query will result in something like this

WHERE date_start>="2016-09-13" and date_end<="2016-09-13"

Are you sure you're looking for those records?

That is exactly why you get good results when you remove one where clause related to the dates - those events are not starting and ending on the same date.

As confirmed in comments, this actually is the problem. WHERE clause is not logical according to the requirements. Solution is in using orWhere

Comments