Now that cards have been reactified we can consider adding them to archetype pages (over the "top 10 cards" list) and elsewhere (tournaments?! anything!)

[vorpal-buildbot]

Reported on Discord by bakert#2193

[bakert]

/api/decks and DeckTable have archetypeId and it could be added to the equivalent places for cards.

[bakert]

It's not quite this simple because load_cards talks to two preaggreagated tables. It doesn't do anything on user time. So we'd need to either change load_cards to sometimes do a user time query (dangerous?) or create an _arch_card_stats table or similar to read from the /api/cards2 code.

[bakert]

I ran a version of the query for archetype on local for season 17 mono black aggro and it took 7 seconds. So this is a cache table option only.

SELECT
    card AS name,
    season.id AS season_id,
    d.archetype_id,
    SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
    IFNULL(SUM(wins), 0) AS wins,
    IFNULL(SUM(losses), 0) AS losses,
    IFNULL(SUM(draws), 0) AS draws,
    SUM(CASE WHEN wins >= 5 AND losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
    SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
    SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
    (CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
    deck AS d
INNER JOIN
    (SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
    LEFT JOIN
        competition AS c ON d.competition_id = c.id
    LEFT JOIN
        competition_series AS cs ON cs.id = c.competition_series_id
    LEFT JOIN
        competition_type AS ct ON ct.id = cs.competition_type_id
    LEFT JOIN
        (
            SELECT
                `start`.id,
                `start`.code,
                `start`.start_date AS start_date,
                `end`.start_date AS end_date
            FROM
                season AS `start`
            LEFT JOIN
                season AS `end` ON `end`.id = `start`.id + 1
        ) AS season ON season.start_date <= d.created_date AND (season.end_date IS NULL OR season.end_date > d.created_date)
    LEFT JOIN
        (
            SELECT
                d.id,
                d.created_date,
                d.finish,
                SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins,
                SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
                SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
            FROM
                deck_match AS dm
            INNER JOIN
                deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
            INNER JOIN
                deck AS d ON d.id = dm.deck_id
            GROUP BY
                d.id
        ) AS dsum ON d.id = dsum.id
WHERE
    season.id = 17 AND d.archetype_id = 13
GROUP BY
    card,
    d.archetype_id,
    season.id,
    ct.name

[bakert]

18s for the above query on prod. 1 min 39s for the full by-archetype by-season (no WHERE clause) query. That's a small enough addition to renew_cache that I'm tempted to add it despite the perf issues we see there currently.