将子查询SQL转换为Laravel可能吗？

Question

Is this possible to change this SQL to let it work on Laravel?

SELECT  name, event,m.season_id, tm.played_pugs, active, m.created_at, datediff(now(),m.created_at)
FROM matchs m
LEFT OUTER JOIN seasons ON seasons.id = m.season_id
JOIN ( SELECT season_id, max(matchs.created_at) as MaxDate, count(season_id) as played_pugs
                  FROM matchs
                  GROUP BY season_id) tm on m.season_id = tm.season_id and m.created_at = tm.MaxDate
ORDER BY played_pugs descc>

This is what I have so far:

$seasons = DB::table('matchs')
    ->select('name', 'event', 'season_id', 'played_pugs', 'active', DB::raw('datediff(now(),created_at) as days'))
    ->join('seasons', 'seasons.id', '=', 'matchs.season_id', 'left outer')
    ->join(DB::raw('SELECT season_id, max(matchs.created_at) as MaxDate, count(season_id) as played_pugs FROM matchs GROUP BY season_id)'), '')
    ->orderBy('played_pugs','desc')
    ->get();

Also I can't see the values with 'played_pugs' that have value 0 anymore. How can I fix that.

Answer 1

This will generate the exact query you posted. I've built the join subquery using a separate Query Builder object and used the toSql() method to get the generated SQL string to inject using DB::raw(), because it's more readable this way and it's 100% built using the Query Builder syntax.

$joinSubquery = DB::table('matchs')
    ->select('season_id', DB::raw('max(matchs.created_at) as MaxDate'), DB::raw('count(season_id) as 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();