Laravel子查询关联查询中Where条件的正确放置位置咨询

Hey there! Let's break down where to insert that where clause in your query. The key here is understanding how your join and subquery interact, and which part of the data you want to filter.

Option 1: Filter Final Results (After Joining) #

If you want to first fetch each company's latest sales report, then filter out any of those latest reports that are newer than 2016-01-01, add the where clause right after the join closure, but before your FilterPaginateOrder() scope. Be sure to specify the table name to avoid column ambiguity (since periods exists in both your main table and the subquery):

$data = App\SalesReport::with('company')
    ->join(DB::RAW('(SELECT company_id, MAX(periods) AS max_periods FROM laporancu GROUP BY company_id) latest_report'), function($join){
        $join->on('salesreport.company_id','=','latest_report.company_id');
        $join->on('salesreport.periods','=','latest_report.max_periods');
    })
    // Add the where clause here with explicit table reference
    ->where('salesreport.periods', '<=', '2016-01-01')
    ->FilterPaginateOrder();

Option 2: Filter the Subquery (Before Calculating Latest Reports) #

If your goal is to only consider sales reports up to 2016-01-01 when finding each company's latest report (meaning you won't include companies whose latest report falls after that date), add the condition directly inside your subquery:

$data = App\SalesReport::with('company')
    // Add WHERE clause inside the subquery to limit data before calculating max
    ->join(DB::RAW('(SELECT company_id, MAX(periods) AS max_periods FROM laporancu WHERE periods <= \'2016-01-01\' GROUP BY company_id) latest_report'), function($join){
        $join->on('salesreport.company_id','=','latest_report.company_id');
        $join->on('salesreport.periods','=','latest_report.max_periods');
    })
    ->FilterPaginateOrder();

Which Option Fits Your Needs? #

• Use Option 1 if you want to include all companies (even those with a latest report after 2016-01-01) but only display their latest report if it's on or before the target date.
• Use Option 2 if you want to exclude any company whose latest report is after 2016-01-01 entirely — this is usually more efficient since it reduces the data processed in the subquery upfront.

内容的提问来源于stack exchange，提问作者PamanBeruang