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
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();