whereBetween Dates in laravel 4 eloquent

Question

I have a query like that

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 I can convert this query in laravel 4. I use Eloquent

Answer 1

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

Answer 2

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 3

In your example, you're checking both from_date and to_date for the same range of dates...if this will always be the case, you can make this query a bit 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 a controller:

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

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 first argument of whereBetween(), but I'm not sure if it's possible, in that case you can use orWhere() with a closure to write a 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.