Forum Multiple Joins from Two Tables

learner

posted 9 years ago

Database Eloquent

Hi, everyone.

I need to implement the following list.

No | Subject | Provider | Replier
1 | Subject1 |   User1   | User2
2 | Subject2 |   User8   |    -

This data is in "subjects" table, in which "provider" and "replier" fields are primary keys of "users" table.

The "replier" field might be null, but "provider" always has value.

Originally, I implemented this query as follows:

SELECT a.*, b.user_name as replier_name
FROM (SELECT `users`.user_name as provider_name, `subjects`.*
    FROM `users`, `subjects`
    WHERE `users`.user_id = `subjects`.provider) as a LEFT JOIN `users` as b
ON (`users`.user_id = `subjects`.replier);

I wonder how this SQL query can be implemented using Laravel's Query Builder.

Please help me.

Thanks.

Last updated 3 years ago.

learner

replied 9 years ago

Solution

OK. I figured it out by myself.

$subject_list = DB::table('subjects')
->join('users as provider', 'provider.user_id', '=', 'subjects.provider')
->leftjoin('users as replier', 'replier.user_id', '=', 'subjects.replier')
->select('provider.user_name as provider_name', 'replier.user_name as replier_name', 'subjects.*')
->get();