Where between the dates in the caravan 4 eloquent

Question

I have such a request

SELECT * FROM `sp_price` WHERE (`from_date` between '2014-08-15' and '2014-09-18') || (`to_date` between '2014-08-15' and '2014-09-18') 

Now how can I convert this request to laravel 4 . I am using Eloquent

Answer 1

 DB::table(sp_price) ->whereBetween('from_date',array('2014-08-15','2014-08-18')) ->orWhereBetween('to_date',array('2014-08-15','2014-08-15')) ->get(); 

maybe you can try this

Answer 2

In your example, you check both from_date and to_date for the same date range ... If that is always the case, you can make this query a little more "eloquent":

In the SpPrice.php model:

 public function getPriceByDate($fromDate, $toDate) { $range = [$fromDate, $toDate]; return $this ->whereBetween('from_date', $range) ->orwhereBetween('to_date', $range) ->get(); } 

Then, to call this method from the controller:

  $prices = new SpPrice; $price = $prices->getPriceByDate('2014-08-15', '2014-09-18'); 

Answer 3

  $count = TokenLog::whereBetween(DB::raw('date(created_at)'), [$start_date, $end_date])->get(); 

Answer 4

You can use whereRaw() to add a raw where clause to the query, for example:

 $results = SpPrice::whereRaw("('2014-08-15' between `from_date` and `to_date`) || ('2014-09-18' between `from_date` and `to_date`)")->get(); 

Or maybe you can use DB::raw() as the first argument to whereBetween() , but I'm not sure if this is possible, in this case you can use orWhere() with closure to write more readable code, for example

 SpPrice::whereBetween(DB::raw('"2014-08-15"'), ['from-date', 'to_date'])->orWhere(function($q) { $q->whereBetween(DB::raw('"2014-09-18"'), ['from-date', 'to_date']); }); 

But I'm not quite sure if this works, give it a try.