How to write such a query

SELECT COUNT(*) FROM (SELECT * FROM `drivers` where user_id = 1 order by trawel_date desc LIMIT 10) as tmp where (way1 = 25 or way2 = 25 or way3 = 25 )

using query builder Laravel 5? I can not figure out how to replace the FROM. The construction of the query starts with $this->drivers and this is equivalent to SELECT * FROM drivers . Tried through DB::raw transfer internal SELECT - does not work. And tried so

 $tmp = $this->drivers->latest('trawel_date') ->where('user_id', '=', 1)->take(10)->get(); return $tmp->where('way1','=',25) ->orwhere('n2','=',25) ->orwhere('n3','=',25)->count();

the error is obtained. Is it possible to construct such a query? I really do not want to leave a request in this form. It would be nice, in a laravel way :)

  • It would be nice if you specified what kind of error is obtained. - VenZell
  • The Call to undefined method Illuminate\Database\Eloquent\Collection::orwhere() is because the collection is returned. - Konstantin

1 answer 1

The construction of the query begins with

 $tmp = Drivers::latest(..)->...

Besides

 $tmp->where(..)->..

you can’t do it anymore since tmp is already a collection after running get() , and you need a Builder . Those. either make a request from the beginning, or for example:

  $tmp = Drivers::latest('trawel_date') ->where('user_id', 1) ->take(10); $result = $tmp->get(); return $tmp->where('way1', 25) ->orWhere('n2', 25) ->orWhere('n3', 25) ->count();

UPD

You can also filter by n1, n2, n3 already received collection

  $tmp = Drivers::latest('trawel_date') ->where('user_id', 1) ->take(10)->get(); $result = $tmp->filter(function ($driver) { return ($driver->n1 == 25 || $driver->n2 == 25 || $driver->n3 == 25); }); return $result->count();
  • Thanks for the answer. But the fact is that the conditions or where I need to apply it to the 10 selected lines that are in $ result. Only one way out is obtained - in foreach, to process $ result? - Konstantin
  • corrected the request - apelsinka223
  • You do not mind, what should be orWhere ? - VenZell
  • Incorrectly counts. \DB::listen(function($sql) { var_dump($sql); }); shows what select * from drivers where user_id = ? order by travel_date desc limit 10 select * from drivers where user_id = ? order by travel_date desc limit 10 and select count(*) as aggregate from drivers where user_id = ? and n1 = ? or n2 = ? or n3 = ? order by travel_date desc limit 10 select count(*) as aggregate from drivers where user_id = ? and n1 = ? or n2 = ? or n3 = ? order by travel_date desc limit 10 select count(*) as aggregate from drivers where user_id = ? and n1 = ? or n2 = ? or n3 = ? order by travel_date desc limit 10 and answer = 181 but should be 3 - Konstantin
  • Updated the answer, try the version below - apelsinka223