CODEWITHSUNDEEP
laraveleloquent
user173457
user173457

Reputation: 165

Laravel join two table from two columns

How do I achieve something like this using Laravel Eloquent? I didn't find how to name the same table twice using Eloquent.

Thanks.

Table users

id | first_name | last_name |
---+------------+-----------+
 1 | John       | Doe       |
 2 | Jane       | Doe       |
 3 | Some       | Name      |

Table stamp

id |    date    | applicant_id  | app_by_id |
---+------------+---------------+-----------+
 1 | 2013-03-15 | 1             | 2         |
 2 | 2013-03-10 | 2             | 3         |
 3 | 2013-03-13 | 2             | 1         |

What I want to show:

    date    | applicant | app_by    |
------------+-----------+-----------+
 2013-03-15 | John Doe  | Jane Doe  |
 2013-03-10 | Jane Doe  | Some Name |
 2013-03-13 | Jane Doe  | John Doe  |

Desired equivalent SQL query:

SELECT s.date,
CONCAT_WS(' ', NULLIF(u1.first_name, ' '), NULLIF(u1.last_name, ' ')) AS applicant,
CONCAT_WS(' ', NULLIF(u2.first_name, ' '), NULLIF(u2.last_name, ' ')) AS app_by
FROM stamp s
LEFT JOIN users u1 ON s.applicant_id = u1.id
LEFT JOIN users u2 ON s.app_by_id = u2.id

Upvotes: 0

Views: 7364

Answers (3)

Enrico
Enrico

Reputation: 131

I know this answer is long overdue, but for those that might still be looking for similar solution this should help... A Careful review of the Laravel official documentation for join queries, Below is what I came up with... I have tested the solution and got the expected result

$stamps = Stamp::select('stamps.date')
        ->addSelect(DB::raw("CONCAT_WS(' ', NULLIF(u1.first_name, ' '), NULLIF(u1.last_name, ' ')) AS applicant"))
        ->addSelect(DB::raw("CONCAT_WS(' ', NULLIF(u2.first_name, ' '), NULLIF(u2.last_name, ' ')) AS app_by"))
        ->join(DB::raw("users AS u1"),"u1.id","=","stamps.applicant_id")
        ->join(DB::raw("users AS u2"), "u2.id","=","stamps.app_by_id")->get();

For more in-depth details on join statements you can lookup the official Laravel Documentation also, for more on addSelect See Raw Methods section of the official documentation

Upvotes: 0

Fazal Rasel
Fazal Rasel

Reputation: 4526

You can join table like this-

$stamp = Stamp::join('users as application', 'application.id', '=', 'stamps.applicant_id')
    ->join('users as app_by', 'app_by.id', '=', 'stamps.app_by_id')
    ->select(
            'data',
            DB::raw("CONCAT(application.first_name,' ', application.last_name) as application"),
            DB::raw("CONCAT(app_by.first_name,' ', app_by.last_name) as app_by")
    )
    ->get();

Now, If you like to do this by defining relation on model then-

class Stamp extends Eloquent {

    public function application()
    {
        return $this->belongsTo('User', 'applicant_id');
    }

    public function appBy(){
        return $this->belongsTo('User', 'app_by_id');
    }

}

query

$stamp = Stamp::with('application', 'appBy')->get();
foreach($stamp as $s){
        echo $s->data . '    ';
        echo $s->application->first_name. ' ', $s->application->last_name. '    ';
        echo $s->appBy->first_name. ' ', $s->appBy->last_name;
        echo '<br>';
}

Upvotes: 2

Pᴇʜ
Pᴇʜ

Reputation: 57673

Why don't you use the raw SQL statement like this if you already have it?

$data = DB::Select("SELECT s.date,
    CONCAT_WS(' ', NULLIF(u1.first_name, ' '), NULLIF(u1.last_name, ' ')) AS applicant,
    CONCAT_WS(' ', NULLIF(u2.first_name, ' '), NULLIF(u2.last_name, ' ')) AS app_by
    FROM stamp s
    LEFT JOIN users u1 ON s.applicant_id = u1.id
    LEFT JOIN users u2 ON s.app_by_id = u2.id;"
);

Eloquent was made to make your life easier. If you try to force an extensive SQL statement into Eloquent it doesn't make your life easier.

Upvotes: 1

Related Questions