Laravel subquery join with where

so i am creating this kind of query

$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');
})->FilterPaginateOrder();

and i want to put where('periods','<=','2016-01-01') but i have no idea where should i put it...

i tried to put it after with('company') and it won't returning salesreport at 2015 even thought i have some of them in database. So after experimenting i think i know why it is happening.

Because i have $join->on('salesreport.periods','=','latest_report.max_periods'); and even thought salesreport.periods have periods less than equal 2016-01-01 but because of that join it will always match maximum periods from latest_report

so i think i should add those where clause when the query executing

DB::RAW('(SELECT company_id, MAX(periods) AS max_periods FROM laporancu GROUP BY company_id) latest_report')

but how? where should i put it?

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/2GRXXt6
via IFTTT