Function getPlayerUsage

app/player/player.service.js:92–218

(season, conference, position, school, playerId, excludeGarbageTime)

    };

    const getPlayerUsage = async (season, conference, position, school, playerId, excludeGarbageTime) => {
        let filters = [];
        let params = [];
        let index = 1;

        if (season) {
            filters.push(`g.season = $${index}`);
            params.push(season);
            index++;
        }

        if (conference) {
            filters.push(`LOWER(c.abbreviation) = LOWER($${index})`);
            params.push(conference);
            index++;
        }

        if (position) {
            filters.push(`LOWER(po.abbreviation) = LOWER($${index})`);
            params.push(position);
            index++;
        }

        if (school) {
            filters.push(`LOWER(t.school) = LOWER($${index})`);
            params.push(school);
            index++;
        }

        if (playerId) {
            filters.push(`a.id = $${index}`);
            params.push(playerId);
            index++;
        }
        
        let filter = filters.length ? `WHERE ${filters.join(' AND ')}` : '';

        const results = await db.any(`
        WITH plays AS (
            SELECT DISTINCT g.season,
                            t.id AS team_id,
                            t.school,
                            c.name AS conference,
                            a.id,
                            a.name,
                            po.abbreviation AS position,
                            COUNT(DISTINCT p.id) AS plays,
                            COUNT(DISTINCT p.id) FILTER(WHERE p.play_type_id IN (3,4,6,7,24,26,36,51,67)) AS pass_plays,
                            COUNT(DISTINCT p.id) FILTER(WHERE p.play_type_id IN (5,9,29,39,68)) AS rush_plays,
                            COUNT(DISTINCT p.id) FILTER(WHERE p.down = 1) AS first_downs,
                            COUNT(DISTINCT p.id) FILTER(WHERE p.down = 2) AS second_downs,
                            COUNT(DISTINCT p.id) FILTER(WHERE p.down = 3) AS third_downs,
                            COUNT(DISTINCT p.id) FILTER(WHERE (p.down = 2 AND p.distance >= 8) OR (p.down IN (3,4) AND p.distance >= 5)) AS passing_downs,
                            COUNT(DISTINCT p.id) FILTER(WHERE p.distance < 5 OR (p.down = 2 AND p.distance < 8)) AS standard_downs
            FROM game AS g
                INNER JOIN game_team AS gt ON g.id = gt.game_id
                INNER JOIN team AS t ON gt.team_id = t.id
                INNER JOIN conference_team AS ct ON t.id = ct.team_id AND ct.start_year <= g.season AND (ct.end_year >= g.season OR ct.end_year IS NULL)