How to create a subquery using Laravel Eloquent?

eloquent, laravel, laravel-4, php

Solution

This is how you do a subquery where:

$q->where('price_date', function($q) use ($start_date)
{
   $q->from('benchmarks_table_name')
    ->selectRaw('min(price_date)')
    ->where('price_date', '>=', $start_date)
    ->where('ticker', $this->ticker);
});

Unfortunately `orWhere` requires explicitly provided `$operator`, otherwise it will raise an error, so in your case:

$q->orWhere('price_date', '=', function($q) use ($start_date)
{
   $q->from('benchmarks_table_name')
    ->selectRaw('min(price_date)')
    ->where('price_date', '>=', $start_date)
    ->where('ticker', $this->ticker);
});

EDIT: You need to specify `from` in the closure in fact, otherwise it will not build correct query.

Problem

I have the following Eloquent query (This is a simplified version of a query which consists of of more `where`s and `orWhere`s hence the apparent roundabout way of going about this - the theory is what's important): ``` $start_date = //some date; $prices = BenchmarkPrice::select('price_date', 'price') ->orderBy('price_date', 'ASC') ->where('ticker', $this->ticker) ->where(function($q) use ($start_date) { // some wheres... $q->orWhere(function($q2) use ($start_date){ $dateToCompare = BenchmarkPrice::select(DB::raw('min(price_date) as min_date')) ->where('price_date', '>=', $start_date) ->where('ticker', $this->ticker) ->pluck('min_date'); $q2->where('price_date', $dateToCompare); }); }) ->get(); ``` As you can see I `pluck` the earliest date that occurs on or after my `start_date`. This results in a seperate query being run to get this date which is then used as a parameter in the main query. Is there a way in eloquent to embed the queries together to form a subquery and thus only 1 database call rather than 2? Edit: As per @Jarek's answer this is my query: ``` $prices = BenchmarkPrice::select('price_date', 'price') ->orderBy('price_date', 'ASC') ->where('ticker', $this->ticker) ->where(function($q) use ($start_date, $end_date, $last_day) { if ($start_date) $q->where('price_date' ,'>=', $start_date); if ($end_date) $q->where('price_date' ,'<=', $end_date); if ($last_day) $q->where('price_date', DB::raw('LAST_DAY(price_date)')); if ($start_date) $q->orWhere('price_date', '=', function($d) use ($start_date) { // Get the earliest date on of after the start date $d->selectRaw('min(price_date)') ->where('price_date', '>=', $start_date) ->where('ticker', $this->ticker); }); if ($end_date) $q->orWhere('price_date', '=', function($d) use ($end_date) { // Get the latest date on or before the end date $d->selectRaw('max(price_date)') ->where('price_date', '<=', $end_date) ->where('ticker', $this->ticker); }); }); $this->prices = $prices->remember($_ENV['LONG_CACHE_TIME'])->get(); ``` The `orWhere` blocks are causing all parameters in the query to suddenly become unquoted. E.g. `WHERE`price_date`>= 2009-09-07`. When I remove the `orWheres` the query works fine. Why is this?

Original source