53

How do I convert the following query to Laravel 4 eloquent ORM?

select * from table where ((starttime <= ? and endtime >= ?) or (starttime <= ? and endtime >= ?) or (starttime >= ? and endtime <= ?))
Bishwarup Das
  • 681
  • 1
  • 12
  • 21

2 Answers2

139

Like this:

<?php

$results = DB::table('table')
             ->where(function($query) use ($starttime,$endtime){
                 $query->where('starttime', '<=', $starttime);
                 $query->where('endtime', '>=', $endtime);
             })
             ->orWhere(function($query) use ($otherStarttime,$otherEndtime){
                 $query->where('starttime', '<=', $otherStarttime);
                 $query->where('endtime', '>=', $otherEndtime);
             })
             ->orWhere(function($query) use ($anotherStarttime,$anotherEndtime){
                 $query->where('starttime', '>=', $anotherStarttime);
                 $query->where('endtime', '<=', $anotherEndtime);
             })
             ->get();

Have a look at the documentation for even more cool stuff you can do with Eloquent and the Query Builder.

//Edit: To even wrap the whole where-clause in braces (like it is in your question), you can do this:

<?php

$results = DB::table('table')
             //this wraps the whole statement in ()
             ->where(function($query) use ($starttime,$endtime, $otherStarttime,$otherEndtime, $anotherStarttime,$anotherEndtime){

                 $query->where(function($query) use ($starttime,$endtime){
                     $query->where('starttime', '<=', $starttime);
                     $query->where('endtime', '>=', $endtime);
                 });

                 $query->orWhere(function($query) use ($otherStarttime,$otherEndtime){
                     $query->where('starttime', '<=', $otherStarttime);
                     $query->where('endtime', '>=', $otherEndtime);
                 });

                 $query->orWhere(function($query) use ($anotherStarttime,$anotherEndtime){
                     $query->where('starttime', '>=', $anotherStarttime);
                     $query->where('endtime', '<=', $anotherEndtime);
                 });
             })
             ->get();
David Gras
  • 958
  • 2
  • 12
  • 21
Quasdunk
  • 14,944
  • 3
  • 36
  • 45
  • But when we are using closure, the framework takes care of $query. how do we handle $startTime and $endTime. It throws an error Undefined variable: dateRange on the other hand if I pass the parameter like function($query,$startTime) {} it gives Missing argument 2 for Model::{closure}() – Bishwarup Das Mar 11 '14 at 10:29
  • 2
    @BishwarupDas You can pass external variables into the scope of a Closure like this: `$query->where(function($query) use ($starttime, $endtime) { ... });` Here are some expamples: http://www.php.net/manual/en/functions.anonymous.php (Especially Example #3) – Quasdunk Mar 11 '14 at 10:33
  • select d1.update_id from ( select update_id, count(update_id) as ct from updates_tags where tag_id in (67,33,86,55) group by update_id) as d1 where d1.ct=4 How to convert this into laravel query...? – Ankit Mar 30 '16 at 12:56
1

$jkw = DB::table('table') //this wraps the whole statement in () ->where(function($query) use ($starttime,$endtime, $otherStarttime,$otherEndtime, $anotherStarttime,$anotherEndtime){

             $query->where(function($query) use ($starttime,$endtime){
                 $query->where('starttime', '<=', $starttime)
                 ->where('endtime', '>=', $endtime);
             });

             $query->orWhere(function($query) use ($otherStarttime,$otherEndtime){
                 $query->where('starttime', '<=', $otherStarttime)
                 ->where('endtime', '>=', $otherEndtime);
             });

             $query->orWhere(function($query) use ($anotherStarttime,$anotherEndtime){
                 $query->where('starttime', '>=', $anotherStarttime)
                 ->where('endtime', '<=', $anotherEndtime);
             });
         })
         ->get();

ez pz XD

Yudian Vantoni
  • 11
  • 1
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 19 '22 at 01:25