php - Convert Subquery SQL to Laravel possible? -

is possible change sql let work on laravel?

select  name, event,m.season_id, tm.played_pugs, active, m.created_at, datediff(now(),m.created_at) matchs m left outer join seasons on seasons.id = m.season_id join ( select season_id, max(matchs.created_at) maxdate, count(season_id) played_pugs                   matchs                   group season_id) tm on m.season_id = tm.season_id , m.created_at = tm.maxdate order played_pugs descc> 

this have far:

$seasons = db::table('matchs')     ->select('name', 'event', 'season_id', 'played_pugs', 'active', db::raw('datediff(now(),created_at) days'))     ->join('seasons', 'seasons.id', '=', 'matchs.season_id', 'left outer')     ->join(db::raw('select season_id, max(matchs.created_at) maxdate, count(season_id) played_pugs matchs group season_id)'), '')     ->orderby('played_pugs','desc')     ->get(); 

also can't see values 'played_pugs' have value 0 anymore. how can fix that.

this generate exact query posted. i've built join subquery using separate query builder object , used tosql() method generated sql string inject using db::raw(), because it's more readable way , it's 100% built using query builder syntax.

$joinsubquery = db::table('matchs')     ->select('season_id', db::raw('max(matchs.created_at) maxdate'), db::raw('count(season_id) played_pugs'))     ->groupby('season_id')     ->tosql();  $seasons = db::table('matchs m')     ->select('name', 'event', 'm.season_id', 'tm.played_pugs', 'active', 'm.created_at', db::raw('datediff(now(), m.created_at)'))     ->leftjoin('seasons', 'season.id', '=', 'm.season_id', '')     ->join(db::raw('(' . $joinsubquery . ') tm'), function ($join)     {         $join->on('m.season_id', '=', 'tm.season_id');         $join->on('m.created_at', '=', 'tm.maxdate');     })     ->orderby('played_pugs', 'desc')     ->get();