Laravel join two table from two columns

Question

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

If you provide info about the two tables and a pure SQL-query describing what you want to accomplish with Eloquent, it would be easier to answer. Most likely however, you'll need to use the query builder. See this: stackoverflow.com/questions/22842977/… — Andreas Bergström
– Andreas Bergström, Commented Nov 12, 2014 at 6:06
@AndreasBergström Sorry I didn't put a clear example. The question has been edited. — user173457
– user173457, Commented Nov 12, 2014 at 8:02
Why don't you use simple belongsToMany relationship and deal with joining first and last names in the program layer? Why don't you use join (inner` but want left joins? Why do you want to use Eloquent for something that it isn't supposed to work with? — Jarek Tkaczyk
– Jarek Tkaczyk, Commented Nov 12, 2014 at 11:19

Fazal Rasel · Accepted Answer · 2014-11-12 10:18:11Z

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 . '&nbsp;&nbsp;&nbsp;&nbsp;';
        echo $s->application->first_name. ' ', $s->application->last_name. '&nbsp;&nbsp;&nbsp;&nbsp;';
        echo $s->appBy->first_name. ' ', $s->appBy->last_name;
        echo '<br>';
}

Pᴇʜ · Accepted Answer · 2014-11-12 08:35:37Z

1

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.

answered Nov 12, 2014 at 8:35

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Comments

Enrico · Accepted Answer · 2022-07-06 17:40:37Z

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

Collectives™ on Stack Overflow

Laravel join two table from two columns

3 Answers 3

query

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

query

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related