CODEWITHSUNDEEP
laravellaravel-4query-builder
user3189734
user3189734

Reputation: 665

Laravel 4 advanced wheres

I have my query working perfectly in mySQL, however am struggling to use the advanced wheres inside the laravel query builder.

Can anyone assist in converting this at all?

So far I have:

LARAVEL

$start_request = '2014-12-18 09:00';
$end_request = '2014-12-18 10:00';

$events=  DB::table('events')
          ->where('hotel_id', 4)
          ->orWhere(function($query)
                {
                    $query->where($start_request, '<=', 'start_time')
                              ->where($end_request, '>', 'start_time');
                })
            ->orWhere(function($query)
                 {
                     $query->where($start_request, '>=', 'start_time')
                                ->where($end_request, '<', 'end_time');
                 })
            ->get();

mySQL

SELECT id, title, description, start_time, end_time FROM events where hotel_id = 4 and (('2014-12-18 09:00' <= start_time and '2014-12-18 10:00' > start_time) 
       or ('2014-12-18 09:00' >= start_time and '2014-12-18 10:00' < end_time));

Upvotes: 0

Views: 152

Answers (2)

Fabrizio D&#39;Ammassa
Fabrizio D&#39;Ammassa

Reputation: 4769

Can you try this?

$start_request = '2014-12-18 09:00';
$end_request = '2014-12-18 10:00';

$events=  DB::table('events')
          ->where('hotel_id', 4)
          ->where(function($query) use($start_request, $end_request) {
                    $query->where(function($subquery) use($start_request, $end_request){
                        $subquery->where('start_time', '>=', $start_request)
                                 ->where('start_time', '<', $end_request);
                    });
                    $query->orWhere(function($subquery1) use($start_request, $end_request) {
                        $subquery1->where('start_time', '<=', $start_request)
                                ->where('end_time', '>', $end_request);
                    });

            })
            ->get();

Upvotes: 1

Marcin Nabiałek
Marcin Nabiałek

Reputation: 111839

Something like this should do the job:

$start_request = '2014-12-18 09:00';
$end_request = '2014-12-18 10:00';

$events = DB::table('events')
    ->where('hotel_id', 4)
    ->where(function ($q) use ($start_request, $end_request) {
        $q->where(function ($query) use ($start_request, $end_request) {
            $query->where($start_request, '<=', 'start_time')
                ->where($end_request, '>', 'start_time');
        })
        ->orWhere(function ($query) use ($start_request, $end_request) {
            $query->where($start_request, '>=', 'start_time')
                ->where($end_request, '<', 'end_time');
        });
    })
    ->select('id', 'title', 'description', 'start_time', 'end_time')
    ->get();

Upvotes: 1

Related Questions