Perform sub query in eloquent?

Question

How can I perform a sub query in eloquent?

I need to convert:

select * from(//complex sub query) as prds order by date desc

It's a massive query so didnt post it. I just need to know how to perform a select with a sub query. — panthro, Sep 16 '15 at 13:36
I don't think you can use Eloquent for that, as Eloquent is connected to a model which in turn is connected to a single table in your database. You're better off using the DB facade. — Chris Magnussen, Sep 16 '15 at 13:40
http://stackoverflow.com/a/31893645/4293929 may the query there help — Mustafa ASAN, Sep 16 '15 at 14:06
Without seeing the query & knowing the details of your models, this question is unanswerable. However, there are a couple of points that might help: 1) you can use query builder expressions with Eloquent models, so `whereRaw` might help; and 2) if you can rewrite your big query so it returns a list of primary keys, you could use `whereIn` or a loop and `find` to build a list of Eloquent models. — Kryten, Sep 16 '15 at 14:09

score 1 · Answer 1 · answered Nov 18 '16 at 19:19

It's tricky to answer this without the specific tables, columns, etc. But in general, the key to doing this kind of thing is that you can pass a function as the second parameter of whereIn() or whereExists(), and that will create a sub-query. For example:

Products::whereExists(function ($query) {
   $query->select(DB::raw(1))->from('orders')
      ->whereRaw('orders.product_id = products.id');
})->get();

Or you can do something with whereIn() like this:

Products::whereIn('id', function ($query) use ($orderID) {
   $query->select('product_id')->from('orders')
      ->where('orders.id', $orderID);
})->get();

score 1 · Answer 2 · answered Aug 30 '17 at 10:39

At first create subquery that you need

$subQuery = DB::table("some_table as s")
    ->select('s.name', DB::raw('count(s.id) as s_count'))
    ->join('other_table as o', 's.id', '=', 'o.val')
    ->where('o.values', $someVal)
    ->groupBy('s.name');

Than can create general query

$rows = DB::table(DB::raw("({$subQuery->toSql()}) as sub"))->mergeBindings($subQuery)
    ->where('sub.name', $val)
    ->orderBy("sub.s_count", 'asc')
    ->get();

Also you can get count if need

$count = DB::table(DB::raw("({$subQuery->toSql()}) as sub"))->mergeBindings($subQuery)
    ->where('sub.name', $val)
    ->count();

Perform sub query in eloquent?

2 Answers2