Use subqueries to assign column values for each record in laravel

Question

As i know, "Subqueries can assign column values for each record".

example: lets consider this database,

user (id, name, age)
user_detail (user_id[foreign], user_email, address)

Now we can select all email and name by subquery like this:

    SELECT id, (SELECT user_email FROM user_detail WHERE user_detail.user_id = user.id LIMIT 1) as email,
    name, age 
       FROM user
          WHERE 1

This will the output table like:

 _ _ _ _ _ _ _ _ _ _ _ _ _
| id | email | name | age |
+-------------------------+
---------All rows----------

Now how can i query with it laravel eloquent?

More specific ... I have some tables,

1. session (id, name)
2. account (id, name, group)
3. action (id, action_name, detail)
4. another_table (id, detail)
5. transaction (id, from_account_id, to_account_id, session_id,
   action_table_id, another_table_id, other_attributes )

now i want a single query to get each line as a object, and hole a array query should return

result (transaction_id, session_name, from_account_name, to_account_name,
        action_name, another_table_detail, other_attributes)

and finally send them to json, such that i read them by just a for loop.

Why do you have to use a subquery? Are you familiar with [Eloquent relationships](https://laravel.com/docs/eloquent-relationships)? — Jonas Staudenmeir, Jan 18 '19 at 15:12
Thanks for you reply #Jonas. I wanna learn subquery with laravel and apply them with 4 to 5 table joining. If i use relation than it goes so expensive with eloquent (as i know). That is the tow reason. Can you help me to reach out? — hasan05, Jan 19 '19 at 18:39
well, more specific ... lets have some tables, 1. session (id, name) 2. account (id, name, group) 3. action (id, action_name, detail) 4. another_table (id, detail) 5. transaction (id, from_account_id, to_account_id, session_id, action_table_id, another_table_id, other_attributes ) now i want a single query to get each line as a object, and hole a array query should return result (transaction_id, session_name, from_account_name, to_account_name, action_name, another_table_detail, other_attributes) and finally send them to json, such that i read them by just a for loop. — hasan05, Jan 19 '19 at 19:09
You can create a subquery with [`selectSub()`](https://github.com/laravel/framework/blob/1d349728e0b89498e93a2c3cbb3ddde0b74ba92e/src/Illuminate/Database/Query/Builder.php#L236). — Jonas Staudenmeir, Jan 20 '19 at 01:39
Thanks a lot. I am looking for this. and it works with selectSub(). — hasan05, Jan 21 '19 at 07:17

score 0 · Answer 1 · answered Jan 15 '19 at 23:44

0

Try this

$users = User::join('users', 'users.id', '=', 'user_detail')->get();

Should join two tables with all the records you need.

answered Jan 15 '19 at 23:44

rkg

805
5
14

Thanks for you reply #rkg. I have this idea and that is a quite good solution. But i want to use it from subqueries. and i am stuck on it. can you help me to reach out? – hasan05 Jan 16 '19 at 16:18

score 0 · Accepted Answer · answered Jan 21 '19 at 08:23

Thanks Jonas-staudenmeir. He helps me to find the solution.

In that case we can use selectSub() method from laravel query builder class. selectSub()

For first one:

    $user_email = DB::table('user_detail')
                ->select('user_detail.user_email')
                ->whereRaw('user_detail.user_id = user.id')
                ->take(1);

    $data = DB::table('user')
            ->select('name', 'age')
            ->selectSub($user_email, 'email') // selectSub(sub_query, as) 
            ->get();

For second one:

    //session query
    $session = DB::table('session')
                ->select('session.name')
                ->whereRaw('transaction.session_id = session.id')
                ->take(1);
    //from_account query
    $from_account = DB::table('account')
                ->select('account.name')
                ->whereRaw('transaction.from_account_id = account.id')
                ->take(1);
    //to_account query
    $to_account = DB::table('account')
                ->select('account.name')
                ->whereRaw('transaction.to_account_id = account.id')
                ->take(1);
    //action query
    $action = DB::table('action')
                ->select('action.action_name')
                ->whereRaw('transaction.action_table_id = action.id')
                ->take(1);
    //another_table query
    $another_table = DB::table('another_table')
                ->select('another_table.detail')
                ->whereRaw('transaction.another_table_id = another_table.id')
                ->take(1);

    $data = DB::table('transaction')
            ->select('transaction.id as transaction_id', 'other_attributes')
            ->selectSub($session, 'session_name') //session as session_name
            ->selectSub($from_account, 'from_account_name') //from_account as from_account_name
            ->selectSub($to_account, 'session_name') //to_account as to_account_name
            ->selectSub($action, 'action_name') //action as action_name
            ->selectSub($another_table, 'another_table_detail') //another_table as another_table_detail
            ->get();

Though we can use join or left join. Which one is faster it all depends on the data, indexes, correlation, amount of data, query, etc. Subqueries can be slower than LEFT [OUTER] JOINS, but in my opinion their strength is slightly higher readability. join-vs-sub-query

Use subqueries to assign column values for each record in laravel

2 Answers2