I'm trying to find a way to convert this raw SQL into a laravel query builder:
SELECT value1,value2
FROM foo
WHERE id NOT IN(
SELECT id
FROM foo
HERE foo.key = 'client_location_id');
I've looked through the laravel docks but there isn't any clear outlined way of converting nexted selects into their query builder version.
You can use whereDoesntHave function:
$c = Foo::whereDoesntHave('fooRelation', function ($query) use ($clientLocationId) {
$query->where('key', clientLocationId);
})->get();
This will work if you set the relationship in Foo model, for example:
public function fooRelation()
{
return $this->hasMany(Foo::class);
}
Laravel's whereNotIn operator also accepts functions to nest queries:
$result = DB::table("foo")
->whereNotIn(function ($query) {
$query->from("foo as f2")
->where("f2.key", "client_location_id")
->select("id");
})->select("value1","value2")->get();
This will produce the following query (you can verify the query you get if you do a ->toSQL() instead of ->get())
select `value1`, `value2`
from `foo`
where `id` not in (
select `id` from `foo` as `f2` where `f2`.`key` = ?
)
Of course I could point out that this particular query does not require a nested select but I'm assuming this is just an example.
This being said, the eloquent ORM is a better long term solution if your project is large.
