Laravel Eloquent ORM WHERE IN (subquery)

Question

I've the following SQL query:

SELECT * from db.tableA WHERE field in (SELECT id FROM db.tableB where other_field = value);

I want to select from tableA where field is in the array of values returned by the subquery. The question is: how can I do this with eloquent? My current solution (which is very ugly I think) is the following:

$a = \App\tableB::where("other_field", "=", $value)->select('id')->get();
$arr = array();
for ($i = 0; $i < count($a); $i++) array_push($arr, $a[$i]['id']);
$res = \App\tableA::whereIn("field", $arr)->get();

There is a better way of doing this?

Thanks!

Solution 1

Lets simplify your code to.

$arr = \App\tableB::where("other_field", "=", $value)->lists('id')->all(); 

$res = \App\tableA::whereIn("field", $arr)->get();

The lists() chained with all() will automatically convert your collection to an array. But wit laravel 5.0 or less you dont need the all() to convert your collection to an array.

Solution 2

One query is better than two queries!

So the following performs a query having a subquery in whereIn clause using eloquent:

\App\tableA::whereIn("field", function ($query) use ($value) {
    $query->select('id')
        ->from('table_b')
        ->where('other_field', $value);
})->get()

Comments

[Okneloper]

This doesn't do a subquery but rather an additional query. When there are more than a few records returned in the first query (a couple of thouands, a million?) your app will struggle. Here's how to a subquery: stackoverflow.com/questions/27064678/laravel-eloquent-subque‌​ry