Build a left join query with using() instead of on()

Question

How do I create a query that joins with using() instead of on?

I have this:

$games = DB::table('games')
    ->leftJoin('game_info', 'game_id')
    ->get();

I would like it to build a query that looks like this:

select * from games left join game_info using(game_id);

Could maybe try `DB::table("games")->select(DB::raw("SELECT * FROM games LEFT JOIN game_info USING(game_id)"))->get();`, see if that works. — Tim Lewis, Dec 31 '15 at 16:41

score 0 · Answer 1 · edited May 23 '17 at 12:04

0

It seems there is no easy way to make join this way if you don't want to create raw query. However looking at SQL documentation and after quick look at this thread using works the same as you defined in both tables same column, so query:

select * from games left join game_info using(game_id);

will work same as

select * from games left join game_info ON games.game_id = game_info.game_id

so you could simply use:

$games = DB::table('games')
    ->leftJoin('game_info', 'games.game_id','=','game_info.game_id')
    ->get();

edited May 23 '17 at 12:04

Community

1
1

answered Dec 31 '15 at 17:01

Marcin Nabiałek

109,655
42
258
291

that says that I am missing argument 2 for `leftJoin` – Get Off My Lawn Dec 31 '15 at 17:04
Yep, sorry, my mistake, deleting this – Marcin Nabiałek Dec 31 '15 at 17:06
it works the same, yes, but it doesn't return the results the same. Using `on` you will get both `game_id` columns from both table. Using `using` you will get the two columns merged as one. – Get Off My Lawn Dec 31 '15 at 18:00
Yes, but when you don't specify columns (and you don't do it in sample code), in fact you'll have available `game_id` only once so it won't make you any difference – Marcin Nabiałek Dec 31 '15 at 18:13
@GetOffMyLawn There's one more thing - it seems `USING` is not standard in SQL and it's not available in all engines so probably that's why there's no builder method to create it – Marcin Nabiałek Dec 31 '15 at 18:18

Build a left join query with using() instead of on()

1 Answers1