Tuesday, May 23, 2017

Laravel join with multiple conditions

I have been scratching my head with this all day.

I have 3 tables, brands, bids and quotes. Each quote has an urgency_time needed. Each bid has a lead_time to ship and a price.

I need to get the bid that has a lead_time that is equal to or lower than the quotes urgency_time. If there is not a lead_time that is equal or lower to the quotes urgency_time than I need to get the next lead_time that is greater or equal to the quotes urgency_time.

This is my query so far:

$quotes = \App\Quote::leftJoin('brands', 'brands.id', '=', 'quotes.brand_id')
     ->leftJoin('bids', function($q) {
     $q->on('bids.quote_id', '=', 'quotes.id')})
     ->select('quotes.*', 
     'brands.name',
     'bids.price',
     'bids.lead_time_id',
     'brand_urgency.name as lead_time_name'
     )
     ->where('quotes.user_id', \Auth::user()->id)->where('quotes.active', 1)
     ->paginate(12);



via user2544143

Advertisement