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


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

But also

 Invoices.STORNO_ID      Coletes.INVOICE_ID
           1                        1

My query looks like this:

$data = DB::table('invoices')

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

>Solution :

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', '')
    ->where(function($query) use ($id) {
        $query->where('coletes.invoice_id', $id)
            ->orWhere('coletes.invoice_id', 'invoices.storno_id')

Leave a Reply Cancel reply