Laravel9 how to join two tables if one of many conditions is met-CodePudding

I have 2 tables, I want to gather all the coletes that have the same invoice_id as the invoices id OR if the invoice_id = invoices storno_id

InvoicesID      Coletes.INVOICE_ID 
1                  1
                   1
                   1

But also

 Invoices.STORNO_ID      Coletes.INVOICE_ID
           1                        1
                                    1
                                    1

My query looks like this:

$data = DB::table('invoices')
->leftJoin('coletes','coletes.invoice_id','=','invoices.id')
->where('coletes.invoice_id','=',$id)
->orWhere('coletes.invoice_id','=','invoices.storno_id')
->get();

But it only returns the coletes with invoice_id = $id, and not also the ones with the same invoice_id as invoices,storno_id

CodePudding user response：

In traditional SQL queries, you can select from multiple tables like so from invoices, coletes. This can be done by using DB::raw() with the query builder. Then adding the join logic, as where logic instead.

DB::table(DB::raw('invoices, coletes'))
    ->where('coletes.invoice_id', 'invoices.id')
    ->where(function($query) use ($id) {
        $query->where('coletes.invoice_id', $id)
            ->orWhere('coletes.invoice_id', 'invoices.storno_id')
    })->get();