Nested select in query builder.

Hi there, still new to laravel and learning more... got a curious one i cant seem to find an answer for.

Currently i have this working:

$job_owner = DB::table('TABLE_A')->select('username')->where('job_id',$request->job_id)->limit(1)->get();

$details = DB::table('TABLE_A')
    ->leftJoin('TABLE_B','TABLE_A.username','=','TABLE_B.username')
    ->select('TABLE_A.job_id','TABLE_A.payments_made','TABLE_A.username','TABLE_A.project_name','TABLE_A.location','TABLE_B.balance')
    ->where('TABLE_A.username',$job_owner[0]->username)
    ->limit(1)
    ->get();

However, that requires 2 DB::table pings which i dont really want/like and i thought i could just do this (below), but it naturally throws the error Object of class stdClass could not be converted to string since that's what ->get() returns.

$details = DB::table('TABLE_A')
    ->leftJoin('TABLE_B','TABLE_A.username','=','TABLE_B.username')
    ->select('TABLE_A.job_id','TABLE_A.payments_made','TABLE_A.username','TABLE_A.project_name','TABLE_A.location','TABLE_B.balance')
    ->where('TABLE_A.username',DB::table('TABLE_A')->select('username')->where('job_id',$request->job_id)->limit(1)->get())
    ->limit(1)
    ->get();

Im trying to avoid using: DB:select( raw mysql select statement here )... because i need to bind $request->job_id, and i'm also uncertain how that's done.

But basically what's the best Query Builder way to achieve the MySQL equivalent of a nested query.

... WHERE username = (SELECT username FROM TABLE WHERE x=y) ....

LaryAI

2 years ago

Best Answer

Level 58

To achieve the MySQL equivalent of a nested query using Laravel's Query Builder, you can use a subquery. Here's how you can modify your code to achieve this:

$details = DB::table('TABLE_A')
    ->leftJoin('TABLE_B', 'TABLE_A.username', '=', 'TABLE_B.username')
    ->select('TABLE_A.job_id', 'TABLE_A.payments_made', 'TABLE_A.username', 'TABLE_A.project_name', 'TABLE_A.location', 'TABLE_B.balance')
    ->where('TABLE_A.username', function ($query) use ($request) {
        $query->select('username')
            ->from('TABLE_A')
            ->where('job_id', $request->job_id)
            ->limit(1);
    })
    ->limit(1)
    ->get();

In the where clause, you can pass a closure to specify a subquery. Inside the closure, you can use the select, from, and where methods to build the subquery.

This way, you can achieve the desired result without making an additional query to fetch the username from TABLE_A.

Note: Make sure to replace 'TABLE_A' and 'TABLE_B' with the actual table names in your database.

curiouspete

2 years ago

Level 1

Well dang, the AI answer works. Scary!

Please or to participate in this conversation.