Laravel eloquent double join returning empty

Question

I have a laravel eloquent db query that I would like to return results based on chained join.

$replies = DB::table('model_replies')
                                ->join('support_tickets', function($join){
                                        $join->on('model_replies.model_id', '=', 'support_tickets.id');
                                        $join->where('model_replies.model', '=', DB::raw('"SupportTicket"'));
                                        })
                                ->join('produce_requests', function($join){
                                        $join->on('model_replies.model_id', '=', 'produce_requests.id');
                                        $join->where('model_replies.model', '=', DB::raw('"ProduceRequest"'));
                                        })

                                ->get();

I followed the example from the docs here.

The problem is when I chain the join statements as suggested here my query gives no results.

A single join works just fine. But I need to be able to chain several of them.

What could I be doing wrong?

Thank you!

Update, I updated the join statement to this: `->join('produce_requests', function($join){ $join->on('model_replies.model_id', '=', 'produce_requests.id') ->where('model_replies.model', "ProduceRequest"); })` by removing the `$join` on `where` as it is in the docs but still nothing changed. — Kha Kali, Jul 03 '21 at 11:47
shouldn't your `$join->where` be `$join->on`? Have a look at this: https://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause — Sumit Wadhwa, Jul 03 '21 at 11:51

Sumit Wadhwa · Accepted Answer · 2021-07-03T12:03:47.503

1

If I understood it correctly. You want to get support_tickets for those model_replies where model_replies.model = SupportTicket

DB::table('model_replies')
     ->leftJoin('support_tickets', function($join){
          $join->on('model_replies.model_id', '=', 'support_tickets.id');
          $join->on('model_replies.model', '=', DB::raw('"SupportTicket"'));
      })
      ->leftJoin('produce_requests', function($join){
          $join->on('model_replies.model_id', '=', 'produce_requests.id');
          $join->where('model_replies.model', '=', DB::raw('"ProduceRequest"'));
       })
       ->get();

edited Jul 03 '21 at 12:03

answered Jul 03 '21 at 11:55

Sumit Wadhwa

2,825
1
20
34

This doesn't work because the third argument for $join->on is considered to be a column binding. Even if I do replace that with `DB::raw('"SupportTicket"')` I still get same result. I can't use two joins. – Kha Kali Jul 03 '21 at 12:01
@KhaKali try with your code but with a `->leftJoin` – Sumit Wadhwa Jul 03 '21 at 12:02
Thank you sir. I do appreciate your guidance. – Kha Kali Jul 03 '21 at 12:07
@KhaKali you're welcome. the reason `->join` didn't work is because it's an inner join, but we needed every row from our main table `model_replies` hence the leftJoin – Sumit Wadhwa Jul 03 '21 at 12:17
@sumit-wadhawa are you able to help me extend this? please check my new question: https://stackoverflow.com/questions/68238956/laravel-multiple-leftjoin-groupby-two-fileds-and-limit-results – Kha Kali Jul 03 '21 at 18:40

Laravel eloquent double join returning empty

1 Answers1