search

PennyDreadfulMTG / Penny-Dreadful-Tools

A suite of tools for the Penny Dreadful MTGO community
https://pennydreadfulmagic.com
MIT License
40 stars 28 forks source link

Some decks are not legal in the season that their deck_cache.season_id or deck.created_date or contents imply #9121

Open vorpal-buildbot opened 3 years ago

vorpal-buildbot commented 3 years ago

Reported on Discord by bakert#2193

bakert commented 3 years ago

Actually quite a few decks are mislabeled.

MariaDB [decksite]> SELECT * FROM _season_card_count WHERE name = 'Necropotence';
+--------------+-----------+-----------+
| name         | season_id | num_decks |
+--------------+-----------+-----------+
| Necropotence |         4 |         1 |
| Necropotence |         5 |       212 |
| Necropotence |         6 |         1 |
+--------------+-----------+-----------+
3 rows in set (0.000 sec)

I suspect this is to do with some slight fuzziness around actual season start times. It's probably ok to just query for every deck that isn't legal in its own season and force them into the right season.

bakert commented 1 year ago 
>>> for i in range (1, 30):
...     print(i, 'Penny Dreadful ' + seasons.season_code(i))
... 
1 Penny Dreadful EMN
2 Penny Dreadful KLD
3 Penny Dreadful AER
4 Penny Dreadful AKH
5 Penny Dreadful HOU
6 Penny Dreadful XLN
7 Penny Dreadful RIX
8 Penny Dreadful DOM
9 Penny Dreadful M19
10 Penny Dreadful GRN
11 Penny Dreadful RNA
12 Penny Dreadful WAR
13 Penny Dreadful M20
14 Penny Dreadful ELD
15 Penny Dreadful THB
16 Penny Dreadful IKO
17 Penny Dreadful M21
18 Penny Dreadful ZNR
19 Penny Dreadful KHM
20 Penny Dreadful STX
21 Penny Dreadful AFR
22 Penny Dreadful MID
23 Penny Dreadful VOW
24 Penny Dreadful NEO
25 Penny Dreadful SNC
26 Penny Dreadful DMU
27 Penny Dreadful BRO
28 Penny Dreadful ONE
29 Penny Dreadful MOM
bakert commented 1 year ago

There's actually 318 of these.

SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 1 AND NOT INSTR(legal_formats, 'Penny Dreadful EMN')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 2 AND NOT INSTR(legal_formats, 'Penny Dreadful KLD')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 3 AND NOT INSTR(legal_formats, 'Penny Dreadful AER')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 4 AND NOT INSTR(legal_formats, 'Penny Dreadful AKH')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 5 AND NOT INSTR(legal_formats, 'Penny Dreadful HOU')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 6 AND NOT INSTR(legal_formats, 'Penny Dreadful XLN')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 7 AND NOT INSTR(legal_formats, 'Penny Dreadful RIX')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 8 AND NOT INSTR(legal_formats, 'Penny Dreadful DOM')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 9 AND NOT INSTR(legal_formats, 'Penny Dreadful M19')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 10 AND NOT INSTR(legal_formats, 'Penny Dreadful GRN')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 11 AND NOT INSTR(legal_formats, 'Penny Dreadful RNA')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 12 AND NOT INSTR(legal_formats, 'Penny Dreadful WAR')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 13 AND NOT INSTR(legal_formats, 'Penny Dreadful M20')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 14 AND NOT INSTR(legal_formats, 'Penny Dreadful ELD')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 15 AND NOT INSTR(legal_formats, 'Penny Dreadful THB')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 16 AND NOT INSTR(legal_formats, 'Penny Dreadful IKO')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 17 AND NOT INSTR(legal_formats, 'Penny Dreadful M21')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 18 AND NOT INSTR(legal_formats, 'Penny Dreadful ZNR')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 19 AND NOT INSTR(legal_formats, 'Penny Dreadful KHM')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 20 AND NOT INSTR(legal_formats, 'Penny Dreadful STX')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 21 AND NOT INSTR(legal_formats, 'Penny Dreadful AFR')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 22 AND NOT INSTR(legal_formats, 'Penny Dreadful MID')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 23 AND NOT INSTR(legal_formats, 'Penny Dreadful VOW')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 24 AND NOT INSTR(legal_formats, 'Penny Dreadful NEO')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 25 AND NOT INSTR(legal_formats, 'Penny Dreadful SNC')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 26 AND NOT INSTR(legal_formats, 'Penny Dreadful DMU')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 27 AND NOT INSTR(legal_formats, 'Penny Dreadful BRO')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 28 AND NOT INSTR(legal_formats, 'Penny Dreadful ONE')
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 29 AND NOT INSTR(legal_formats, 'Penny Dreadful MOM');
bakert commented 1 year ago

Lots are tappedout or mtggoldfish entries. But 127 are Gatherling or League.

SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 1 AND NOT INSTR(legal_formats, 'Penny Dreadful EMN') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 2 AND NOT INSTR(legal_formats, 'Penny Dreadful KLD') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 3 AND NOT INSTR(legal_formats, 'Penny Dreadful AER') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 4 AND NOT INSTR(legal_formats, 'Penny Dreadful AKH') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 5 AND NOT INSTR(legal_formats, 'Penny Dreadful HOU') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 6 AND NOT INSTR(legal_formats, 'Penny Dreadful XLN') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 7 AND NOT INSTR(legal_formats, 'Penny Dreadful RIX') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 8 AND NOT INSTR(legal_formats, 'Penny Dreadful DOM') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 9 AND NOT INSTR(legal_formats, 'Penny Dreadful M19') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 10 AND NOT INSTR(legal_formats, 'Penny Dreadful GRN') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 11 AND NOT INSTR(legal_formats, 'Penny Dreadful RNA') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 12 AND NOT INSTR(legal_formats, 'Penny Dreadful WAR') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 13 AND NOT INSTR(legal_formats, 'Penny Dreadful M20') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 14 AND NOT INSTR(legal_formats, 'Penny Dreadful ELD') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 15 AND NOT INSTR(legal_formats, 'Penny Dreadful THB') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 16 AND NOT INSTR(legal_formats, 'Penny Dreadful IKO') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 17 AND NOT INSTR(legal_formats, 'Penny Dreadful M21') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 18 AND NOT INSTR(legal_formats, 'Penny Dreadful ZNR') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 19 AND NOT INSTR(legal_formats, 'Penny Dreadful KHM') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 20 AND NOT INSTR(legal_formats, 'Penny Dreadful STX') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 21 AND NOT INSTR(legal_formats, 'Penny Dreadful AFR') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 22 AND NOT INSTR(legal_formats, 'Penny Dreadful MID') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 23 AND NOT INSTR(legal_formats, 'Penny Dreadful VOW') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 24 AND NOT INSTR(legal_formats, 'Penny Dreadful NEO') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 25 AND NOT INSTR(legal_formats, 'Penny Dreadful SNC') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 26 AND NOT INSTR(legal_formats, 'Penny Dreadful DMU') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 27 AND NOT INSTR(legal_formats, 'Penny Dreadful BRO') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 28 AND NOT INSTR(legal_formats, 'Penny Dreadful ONE') AND source_id IN (2, 3)
UNION
SELECT deck_id, legal_formats, d.url FROM deck_cache AS dc INNER JOIN deck AS d ON dc.deck_id = d.id  WHERE season_id = 29 AND NOT INSTR(legal_formats, 'Penny Dreadful MOM') AND source_id IN (2, 3)
;

+---------+---------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+
| deck_id | legal_formats                                                                                                                   | url                                                 |
+---------+---------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+
|     198 | []                                                                                                                              | http://pennydreadfulmagic.com/competitions/5/       |
|     179 | ["Vintage", "Duel", "Modern", "Legacy"]                                                                                         | http://pennydreadfulmagic.com/competitions/5/       |
|     177 | ["Penny Dreadful ZNR", "Legacy", "Vintage", "Penny Dreadful", "Modern", "Duel", "Penny Dreadful KHM"]                           | http://pennydreadfulmagic.com/competitions/5/       |
|     322 | ["Vintage", "Duel", "Legacy"]                                                                                                   | http://pennydreadfulmagic.com/competitions/7/       |
|     295 | ["Vintage", "Duel", "Legacy"]                                                                                                   | http://pennydreadfulmagic.com/competitions/6/       |
|     278 | ["Duel", "Vintage", "Penny Dreadful", "Penny Dreadful M20", "Penny Dreadful WAR", "Legacy"]                                     | http://pennydreadfulmagic.com/competitions/6/       |
|     235 | []                                                                                                                              | http://pennydreadfulmagic.com/competitions/6/       |
|    2284 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=46687   |
|    2285 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=46046   |
|    2286 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=45792   |
|    1434 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=47110   |
|    2287 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=44522   |
|    2288 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=44577   |
|     964 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=44445   |
|    2289 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=44219   |
|    1092 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=45064   |
|    2290 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=43885   |
|    3257 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=49835   |
|    2280 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=48965   |
|    2281 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=48097   |
|    2282 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=48108   |
|    1671 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=47959   |
|    1563 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=47623   |
|    1545 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=47528   |
|    2283 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=47732   |
|    1496 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=47324   |
|    1474 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=47218   |
|    1450 | ["Vintage", "Duel", "Legacy"]                                                                                                   | http://gatherling.com/deck.php?mode=view&id=47041   |
|    1437 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=47111   |
|    3831 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=50390   |
|    2402 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=49330   |
|    2412 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=49401   |
|    7981 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7940 | ["Penny Dreadful RIX", "Vintage", "Duel", "Modern", "Legacy"]                                                                   | https://pennydreadfulmagic.com/competitions/295/    |
|    7934 | ["Penny Dreadful RIX", "Vintage", "Duel", "Modern", "Penny Dreadful WAR", "Legacy"]                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7971 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | https://pennydreadfulmagic.com/competitions/295/    |
|    7942 | ["Penny Dreadful RIX", "Penny Dreadful WAR"]                                                                                    | https://pennydreadfulmagic.com/competitions/295/    |
|    7960 | ["Modern", "Penny Dreadful RIX", "Vintage", "Duel", "Penny Dreadful RNA", "Legacy"]                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7956 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7996 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7994 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7943 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7999 | ["Penny Dreadful RIX", "Vintage", "Duel", "Modern", "Legacy"]                                                                   | https://pennydreadfulmagic.com/competitions/295/    |
|    7992 | ["Penny Dreadful RIX", "Vintage", "Duel", "Modern", "Legacy"]                                                                   | https://pennydreadfulmagic.com/competitions/295/    |
|    7973 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7935 | ["Vintage", "Penny Dreadful RIX"]                                                                                               | https://pennydreadfulmagic.com/competitions/295/    |
|    7949 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7921 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | https://pennydreadfulmagic.com/competitions/281/    |
|    7983 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | https://pennydreadfulmagic.com/competitions/295/    |
|    7977 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7958 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7988 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7976 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7998 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7962 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7944 | ["Duel", "Penny Dreadful RIX", "Vintage", "Penny Dreadful AER", "Penny Dreadful M20", "Modern", "Penny Dreadful WAR", "Legacy"] | https://pennydreadfulmagic.com/competitions/295/    |
|    7982 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | https://pennydreadfulmagic.com/competitions/295/    |
|    7967 | ["Penny Dreadful RIX"]                                                                                                          | https://pennydreadfulmagic.com/competitions/295/    |
|    7972 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7987 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7954 | ["Duel", "Penny Dreadful RIX", "Vintage", "Penny Dreadful ELD", "Penny Dreadful M20", "Modern", "Penny Dreadful WAR", "Legacy"] | https://pennydreadfulmagic.com/competitions/295/    |
|    7990 | ["Penny Dreadful RIX"]                                                                                                          | https://pennydreadfulmagic.com/competitions/295/    |
|    7993 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7991 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7989 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7984 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7980 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7975 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7986 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7978 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7929 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7985 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7979 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7974 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7932 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7968 | ["Penny Dreadful RIX"]                                                                                                          | https://pennydreadfulmagic.com/competitions/295/    |
|    7964 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | https://pennydreadfulmagic.com/competitions/295/    |
|    7966 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | https://pennydreadfulmagic.com/competitions/295/    |
|    7952 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7965 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7969 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7957 | ["Penny Dreadful RIX", "Vintage", "Penny Dreadful ELD", "Duel", "Modern", "Legacy"]                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7963 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7959 | ["Penny Dreadful RIX"]                                                                                                          | https://pennydreadfulmagic.com/competitions/295/    |
|    7941 | ["Penny Dreadful RIX", "Vintage", "Duel", "Modern", "Legacy"]                                                                   | https://pennydreadfulmagic.com/competitions/295/    |
|    7951 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7946 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7950 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | https://pennydreadfulmagic.com/competitions/295/    |
|    7953 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7955 | ["Penny Dreadful M20", "Penny Dreadful RIX"]                                                                                    | https://pennydreadfulmagic.com/competitions/295/    |
|    7947 | ["Penny Dreadful RIX"]                                                                                                          | https://pennydreadfulmagic.com/competitions/295/    |
|    7931 | ["Penny Dreadful RIX"]                                                                                                          | https://pennydreadfulmagic.com/competitions/295/    |
|    7925 | ["Modern", "Penny Dreadful RIX", "Vintage", "Duel", "Penny Dreadful RNA", "Legacy"]                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7939 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | https://pennydreadfulmagic.com/competitions/295/    |
|    7930 | ["Duel", "Penny Dreadful RIX", "Vintage", "Penny Dreadful AER", "Penny Dreadful M20", "Modern", "Penny Dreadful WAR", "Legacy"] | https://pennydreadfulmagic.com/competitions/295/    |
|    7933 | ["Penny Dreadful RIX", "Vintage", "Duel", "Penny Dreadful WAR", "Legacy"]                                                       | https://pennydreadfulmagic.com/competitions/295/    |
|    7928 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    7927 | ["Duel", "Penny Dreadful RIX", "Vintage", "Penny Dreadful M20", "Penny Dreadful AKH", "Legacy"]                                 | https://pennydreadfulmagic.com/competitions/295/    |
|    7924 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|    9328 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=54808   |
|    5372 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=52444   |
|    5198 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=52247   |
|    9329 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | http://gatherling.com/deck.php?mode=view&id=55483   |
|    4666 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=51534   |
|    4596 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=51474   |
|   13415 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=57926  |
|   13090 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=57674  |
|   13408 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=57929  |
|    8292 | ["Vintage", "Duel", "Penny Dreadful XLN", "Legacy"]                                                                             | http://gatherling.com/deck.php?mode=view&id=54714   |
|    8053 | ["Vintage", "Duel", "Penny Dreadful XLN", "Legacy"]                                                                             | http://gatherling.com/deck.php?mode=view&id=54712   |
|   18583 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=60143  |
|   15244 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=58727  |
|   25461 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=62855  |
|   26929 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=63254  |
|   41512 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=65740  |
|   53166 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=68108  |
|   63562 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=70352  |
|   81503 | ["Vintage", "Legacy", "Penny Dreadful IKO", "Duel"]                                                                             | https://pennydreadfulmagic.com/competitions/3269/   |
|   72406 | ["Legacy", "Vintage"]                                                                                                           | https://pennydreadfulmagic.com/competitions/3191/   |
|   81505 | ["Vintage", "Duel", "Legacy", "Penny Dreadful IKO", "Modern"]                                                                   | https://pennydreadfulmagic.com/competitions/3269/   |
|  109931 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=95894  |
|  110538 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=96056  |
|  112057 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=96502  |
|  119564 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=98699  |
|  119565 | ["Legacy", "Penny Dreadful ZNR", "Duel"]                                                                                        | https://gatherling.com/deck.php?mode=view&id=98645  |
|  144492 | ["Penny Dreadful STX"]                                                                                                          | https://gatherling.com/deck.php?mode=view&id=104419 |
|  208463 | ["Legacy", "Duel", "Penny Dreadful ONE"]                                                                                        | https://pennydreadfulmagic.com/competitions/4233/   |
+---------+---------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+
127 rows in set (0.225 sec)
bakert commented 1 year ago

Better view with better query:

MariaDB [decksite]> SELECT 
    ->   dc.season_id AS `cached`, 
    ->   season.id AS `real`,
    ->   deck_id, 
    ->   legal_formats, 
    ->   d.url 
    -> FROM 
    ->   deck_cache AS dc 
    -> INNER JOIN 
    ->   deck AS d ON dc.deck_id = d.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)
    -> WHERE season_id = 1 AND NOT INSTR(legal_formats, 'Penny Dreadful EMN') AND source_id IN (2, 3)
    ->   OR (season_id = 2 AND NOT INSTR(legal_formats, 'Penny Dreadful KLD') AND source_id IN (2, 3))
    ->   OR (season_id = 3 AND NOT INSTR(legal_formats, 'Penny Dreadful AER') AND source_id IN (2, 3))
    ->   OR (season_id = 4 AND NOT INSTR(legal_formats, 'Penny Dreadful AKH') AND source_id IN (2, 3))
    ->   OR (season_id = 5 AND NOT INSTR(legal_formats, 'Penny Dreadful HOU') AND source_id IN (2, 3))
    ->   OR (season_id = 6 AND NOT INSTR(legal_formats, 'Penny Dreadful XLN') AND source_id IN (2, 3))
    ->   OR (season_id = 7 AND NOT INSTR(legal_formats, 'Penny Dreadful RIX') AND source_id IN (2, 3))
    ->   OR (season_id = 8 AND NOT INSTR(legal_formats, 'Penny Dreadful DOM') AND source_id IN (2, 3))
    ->   OR (season_id = 9 AND NOT INSTR(legal_formats, 'Penny Dreadful M19') AND source_id IN (2, 3))
    ->   OR (season_id = 10 AND NOT INSTR(legal_formats, 'Penny Dreadful GRN') AND source_id IN (2, 3))
    ->   OR (season_id = 11 AND NOT INSTR(legal_formats, 'Penny Dreadful RNA') AND source_id IN (2, 3))
    ->   OR (season_id = 12 AND NOT INSTR(legal_formats, 'Penny Dreadful WAR') AND source_id IN (2, 3))
    ->   OR (season_id = 13 AND NOT INSTR(legal_formats, 'Penny Dreadful M20') AND source_id IN (2, 3))
    ->   OR (season_id = 14 AND NOT INSTR(legal_formats, 'Penny Dreadful ELD') AND source_id IN (2, 3))
    ->   OR (season_id = 15 AND NOT INSTR(legal_formats, 'Penny Dreadful THB') AND source_id IN (2, 3))
    ->   OR (season_id = 16 AND NOT INSTR(legal_formats, 'Penny Dreadful IKO') AND source_id IN (2, 3))
    ->   OR (season_id = 17 AND NOT INSTR(legal_formats, 'Penny Dreadful M21') AND source_id IN (2, 3))
    ->   OR (season_id = 18 AND NOT INSTR(legal_formats, 'Penny Dreadful ZNR') AND source_id IN (2, 3))
    ->   OR (season_id = 19 AND NOT INSTR(legal_formats, 'Penny Dreadful KHM') AND source_id IN (2, 3))
    ->   OR (season_id = 20 AND NOT INSTR(legal_formats, 'Penny Dreadful STX') AND source_id IN (2, 3))
    ->   OR (season_id = 21 AND NOT INSTR(legal_formats, 'Penny Dreadful AFR') AND source_id IN (2, 3))
    ->   OR (season_id = 22 AND NOT INSTR(legal_formats, 'Penny Dreadful MID') AND source_id IN (2, 3))
    ->   OR (season_id = 23 AND NOT INSTR(legal_formats, 'Penny Dreadful VOW') AND source_id IN (2, 3))
    ->   OR (season_id = 24 AND NOT INSTR(legal_formats, 'Penny Dreadful NEO') AND source_id IN (2, 3))
    ->   OR (season_id = 25 AND NOT INSTR(legal_formats, 'Penny Dreadful SNC') AND source_id IN (2, 3))
    ->   OR (season_id = 26 AND NOT INSTR(legal_formats, 'Penny Dreadful DMU') AND source_id IN (2, 3))
    ->   OR (season_id = 27 AND NOT INSTR(legal_formats, 'Penny Dreadful BRO') AND source_id IN (2, 3))
    ->   OR (season_id = 28 AND NOT INSTR(legal_formats, 'Penny Dreadful ONE') AND source_id IN (2, 3))
    ->   OR (season_id = 29 AND NOT INSTR(legal_formats, 'Penny Dreadful MOM') AND source_id IN (2, 3))
    -> ;
+--------+------+---------+---------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+
| cached | real | deck_id | legal_formats                                                                                                                   | url                                                 |
+--------+------+---------+---------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+
|      1 |    1 |     177 | ["Penny Dreadful ZNR", "Legacy", "Vintage", "Penny Dreadful", "Modern", "Duel", "Penny Dreadful KHM"]                           | http://pennydreadfulmagic.com/competitions/5/       |
|      1 |    1 |     179 | ["Vintage", "Duel", "Modern", "Legacy"]                                                                                         | http://pennydreadfulmagic.com/competitions/5/       |
|      1 |    1 |     198 | []                                                                                                                              | http://pennydreadfulmagic.com/competitions/5/       |
|      2 |    2 |     235 | []                                                                                                                              | http://pennydreadfulmagic.com/competitions/6/       |
|      2 |    2 |     278 | ["Duel", "Vintage", "Penny Dreadful", "Penny Dreadful M20", "Penny Dreadful WAR", "Legacy"]                                     | http://pennydreadfulmagic.com/competitions/6/       |
|      2 |    2 |     295 | ["Vintage", "Duel", "Legacy"]                                                                                                   | http://pennydreadfulmagic.com/competitions/6/       |
|      2 |    2 |     322 | ["Vintage", "Duel", "Legacy"]                                                                                                   | http://pennydreadfulmagic.com/competitions/7/       |
|      3 |    3 |     964 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=44445   |
|      3 |    3 |    1092 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=45064   |
|      3 |    3 |    1434 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=47110   |
|      4 |    4 |    1437 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=47111   |
|      4 |    4 |    1450 | ["Vintage", "Duel", "Legacy"]                                                                                                   | http://gatherling.com/deck.php?mode=view&id=47041   |
|      4 |    4 |    1474 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=47218   |
|      4 |    4 |    1496 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=47324   |
|      4 |    4 |    1545 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=47528   |
|      4 |    4 |    1563 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=47623   |
|      4 |    4 |    1671 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=47959   |
|      4 |    4 |    2280 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=48965   |
|      4 |    4 |    2281 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=48097   |
|      4 |    4 |    2282 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=48108   |
|      4 |    4 |    2283 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=47732   |
|      3 |    3 |    2284 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=46687   |
|      3 |    3 |    2285 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=46046   |
|      3 |    3 |    2286 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=45792   |
|      3 |    3 |    2287 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=44522   |
|      3 |    3 |    2288 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=44577   |
|      3 |    3 |    2289 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=44219   |
|      3 |    3 |    2290 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=43885   |
|      5 |    5 |    2402 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=49330   |
|      5 |    5 |    2412 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=49401   |
|      4 |    4 |    3257 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=49835   |
|      5 |    5 |    3831 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=50390   |
|      6 |    6 |    4596 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=51474   |
|      6 |    6 |    4666 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=51534   |
|      6 |    6 |    5198 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=52247   |
|      6 |    6 |    5372 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=52444   |
|      6 |    6 |    7921 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | https://pennydreadfulmagic.com/competitions/281/    |
|      6 |    6 |    7924 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7925 | ["Modern", "Penny Dreadful RIX", "Vintage", "Duel", "Penny Dreadful RNA", "Legacy"]                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7927 | ["Duel", "Penny Dreadful RIX", "Vintage", "Penny Dreadful M20", "Penny Dreadful AKH", "Legacy"]                                 | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7928 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7929 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7930 | ["Duel", "Penny Dreadful RIX", "Vintage", "Penny Dreadful AER", "Penny Dreadful M20", "Modern", "Penny Dreadful WAR", "Legacy"] | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7931 | ["Penny Dreadful RIX"]                                                                                                          | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7932 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7933 | ["Penny Dreadful RIX", "Vintage", "Duel", "Penny Dreadful WAR", "Legacy"]                                                       | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7934 | ["Penny Dreadful RIX", "Vintage", "Duel", "Modern", "Penny Dreadful WAR", "Legacy"]                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7935 | ["Vintage", "Penny Dreadful RIX"]                                                                                               | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7939 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7940 | ["Penny Dreadful RIX", "Vintage", "Duel", "Modern", "Legacy"]                                                                   | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7941 | ["Penny Dreadful RIX", "Vintage", "Duel", "Modern", "Legacy"]                                                                   | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7942 | ["Penny Dreadful RIX", "Penny Dreadful WAR"]                                                                                    | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7943 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7944 | ["Duel", "Penny Dreadful RIX", "Vintage", "Penny Dreadful AER", "Penny Dreadful M20", "Modern", "Penny Dreadful WAR", "Legacy"] | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7946 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7947 | ["Penny Dreadful RIX"]                                                                                                          | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7949 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7950 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7951 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7952 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7953 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7954 | ["Duel", "Penny Dreadful RIX", "Vintage", "Penny Dreadful ELD", "Penny Dreadful M20", "Modern", "Penny Dreadful WAR", "Legacy"] | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7955 | ["Penny Dreadful M20", "Penny Dreadful RIX"]                                                                                    | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7956 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7957 | ["Penny Dreadful RIX", "Vintage", "Penny Dreadful ELD", "Duel", "Modern", "Legacy"]                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7958 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7959 | ["Penny Dreadful RIX"]                                                                                                          | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7960 | ["Modern", "Penny Dreadful RIX", "Vintage", "Duel", "Penny Dreadful RNA", "Legacy"]                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7962 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7963 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7964 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7965 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7966 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7967 | ["Penny Dreadful RIX"]                                                                                                          | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7968 | ["Penny Dreadful RIX"]                                                                                                          | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7969 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7971 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7972 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7973 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7974 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7975 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7976 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7977 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7978 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7979 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7980 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7981 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7982 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7983 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7984 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7985 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7986 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7987 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7988 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7989 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7990 | ["Penny Dreadful RIX"]                                                                                                          | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7991 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7992 | ["Penny Dreadful RIX", "Vintage", "Duel", "Modern", "Legacy"]                                                                   | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7993 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7994 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7996 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7998 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7999 | ["Penny Dreadful RIX", "Vintage", "Duel", "Modern", "Legacy"]                                                                   | https://pennydreadfulmagic.com/competitions/295/    |
|      7 |    7 |    8053 | ["Vintage", "Duel", "Penny Dreadful XLN", "Legacy"]                                                                             | http://gatherling.com/deck.php?mode=view&id=54712   |
|      7 |    7 |    8292 | ["Vintage", "Duel", "Penny Dreadful XLN", "Legacy"]                                                                             | http://gatherling.com/deck.php?mode=view&id=54714   |
|      6 |    6 |    9328 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=54808   |
|      6 |    6 |    9329 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | http://gatherling.com/deck.php?mode=view&id=55483   |
|      7 |    7 |   13090 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=57674  |
|      7 |    7 |   13408 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=57929  |
|      7 |    7 |   13415 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=57926  |
|      8 |    8 |   15244 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=58727  |
|      8 |    8 |   18583 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=60143  |
|      9 |    9 |   25461 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=62855  |
|     10 |   10 |   26929 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=63254  |
|     10 |   10 |   41512 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=65740  |
|     12 |   12 |   53166 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=68108  |
|     14 |   14 |   63562 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=70352  |
|     16 |   16 |   72406 | ["Legacy", "Vintage"]                                                                                                           | https://pennydreadfulmagic.com/competitions/3191/   |
|     15 |   15 |   81503 | ["Vintage", "Legacy", "Penny Dreadful IKO", "Duel"]                                                                             | https://pennydreadfulmagic.com/competitions/3269/   |
|     17 |   17 |   81505 | ["Vintage", "Duel", "Legacy", "Penny Dreadful IKO", "Modern"]                                                                   | https://pennydreadfulmagic.com/competitions/3269/   |
|     18 |   18 |  109931 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=95894  |
|     18 |   18 |  110538 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=96056  |
|     18 |   18 |  112057 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=96502  |
|     19 |   19 |  119564 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=98699  |
|     19 |   19 |  119565 | ["Legacy", "Penny Dreadful ZNR", "Duel"]                                                                                        | https://gatherling.com/deck.php?mode=view&id=98645  |
|     21 |   21 |  144492 | ["Penny Dreadful STX"]                                                                                                          | https://gatherling.com/deck.php?mode=view&id=104419 |
|     27 |   28 |  208463 | ["Legacy", "Duel", "Penny Dreadful ONE"]                                                                                        | https://pennydreadfulmagic.com/competitions/4233/   |
+--------+------+---------+---------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+
127 rows in set (0.381 sec)
bakert commented 1 year ago

A lot of the Gatherling decks are actually illegal and are from when we used to scraper Gatherling decks with 0 matches.

So let's restrict to just decks that have matches.

MariaDB [decksite]> SELECT 
    ->   dc.season_id AS `cached`, 
    ->   season.id AS `real`,
    ->   dc.deck_id, 
    ->   legal_formats, 
    ->   d.url 
    -> FROM 
    ->   deck_cache AS dc 
    -> INNER JOIN 
    ->   deck AS d ON dc.deck_id = d.id
    -> INNER JOIN
    ->   deck_match AS dm ON dm.deck_id = d.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)
    -> WHERE season_id = 1 AND NOT INSTR(legal_formats, 'Penny Dreadful EMN') AND source_id IN (2, 3)
    ->   OR (season_id = 2 AND NOT INSTR(legal_formats, 'Penny Dreadful KLD') AND source_id IN (2, 3))
    ->   OR (season_id = 3 AND NOT INSTR(legal_formats, 'Penny Dreadful AER') AND source_id IN (2, 3))
    ->   OR (season_id = 4 AND NOT INSTR(legal_formats, 'Penny Dreadful AKH') AND source_id IN (2, 3))
    ->   OR (season_id = 5 AND NOT INSTR(legal_formats, 'Penny Dreadful HOU') AND source_id IN (2, 3))
    ->   OR (season_id = 6 AND NOT INSTR(legal_formats, 'Penny Dreadful XLN') AND source_id IN (2, 3))
    ->   OR (season_id = 7 AND NOT INSTR(legal_formats, 'Penny Dreadful RIX') AND source_id IN (2, 3))
    ->   OR (season_id = 8 AND NOT INSTR(legal_formats, 'Penny Dreadful DOM') AND source_id IN (2, 3))
    ->   OR (season_id = 9 AND NOT INSTR(legal_formats, 'Penny Dreadful M19') AND source_id IN (2, 3))
    ->   OR (season_id = 10 AND NOT INSTR(legal_formats, 'Penny Dreadful GRN') AND source_id IN (2, 3))
    ->   OR (season_id = 11 AND NOT INSTR(legal_formats, 'Penny Dreadful RNA') AND source_id IN (2, 3))
    ->   OR (season_id = 12 AND NOT INSTR(legal_formats, 'Penny Dreadful WAR') AND source_id IN (2, 3))
    ->   OR (season_id = 13 AND NOT INSTR(legal_formats, 'Penny Dreadful M20') AND source_id IN (2, 3))
    ->   OR (season_id = 14 AND NOT INSTR(legal_formats, 'Penny Dreadful ELD') AND source_id IN (2, 3))
    ->   OR (season_id = 15 AND NOT INSTR(legal_formats, 'Penny Dreadful THB') AND source_id IN (2, 3))
    ->   OR (season_id = 16 AND NOT INSTR(legal_formats, 'Penny Dreadful IKO') AND source_id IN (2, 3))
    ->   OR (season_id = 17 AND NOT INSTR(legal_formats, 'Penny Dreadful M21') AND source_id IN (2, 3))
    ->   OR (season_id = 18 AND NOT INSTR(legal_formats, 'Penny Dreadful ZNR') AND source_id IN (2, 3))
    ->   OR (season_id = 19 AND NOT INSTR(legal_formats, 'Penny Dreadful KHM') AND source_id IN (2, 3))
    ->   OR (season_id = 20 AND NOT INSTR(legal_formats, 'Penny Dreadful STX') AND source_id IN (2, 3))
    ->   OR (season_id = 21 AND NOT INSTR(legal_formats, 'Penny Dreadful AFR') AND source_id IN (2, 3))
    ->   OR (season_id = 22 AND NOT INSTR(legal_formats, 'Penny Dreadful MID') AND source_id IN (2, 3))
    ->   OR (season_id = 23 AND NOT INSTR(legal_formats, 'Penny Dreadful VOW') AND source_id IN (2, 3))
    ->   OR (season_id = 24 AND NOT INSTR(legal_formats, 'Penny Dreadful NEO') AND source_id IN (2, 3))
    ->   OR (season_id = 25 AND NOT INSTR(legal_formats, 'Penny Dreadful SNC') AND source_id IN (2, 3))
    ->   OR (season_id = 26 AND NOT INSTR(legal_formats, 'Penny Dreadful DMU') AND source_id IN (2, 3))
    ->   OR (season_id = 27 AND NOT INSTR(legal_formats, 'Penny Dreadful BRO') AND source_id IN (2, 3))
    ->   OR (season_id = 28 AND NOT INSTR(legal_formats, 'Penny Dreadful ONE') AND source_id IN (2, 3))
    ->   OR (season_id = 29 AND NOT INSTR(legal_formats, 'Penny Dreadful MOM') AND source_id IN (2, 3))
    -> GROUP BY
    ->   d.id;
+--------+------+---------+---------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+
| cached | real | deck_id | legal_formats                                                                                                                   | url                                                 |
+--------+------+---------+---------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+
|      1 |    1 |     198 | []                                                                                                                              | http://pennydreadfulmagic.com/competitions/5/       |
|      2 |    2 |     278 | ["Duel", "Vintage", "Penny Dreadful", "Penny Dreadful M20", "Penny Dreadful WAR", "Legacy"]                                     | http://pennydreadfulmagic.com/competitions/6/       |
|      2 |    2 |     295 | ["Vintage", "Duel", "Legacy"]                                                                                                   | http://pennydreadfulmagic.com/competitions/6/       |
|      2 |    2 |     322 | ["Vintage", "Duel", "Legacy"]                                                                                                   | http://pennydreadfulmagic.com/competitions/7/       |
|      4 |    4 |    1450 | ["Vintage", "Duel", "Legacy"]                                                                                                   | http://gatherling.com/deck.php?mode=view&id=47041   |
|      4 |    4 |    1474 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=47218   |
|      4 |    4 |    1545 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=47528   |
|      6 |    6 |    4596 | []                                                                                                                              | http://gatherling.com/deck.php?mode=view&id=51474   |
|      6 |    6 |    7921 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | https://pennydreadfulmagic.com/competitions/281/    |
|      6 |    6 |    7924 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7925 | ["Modern", "Penny Dreadful RIX", "Vintage", "Duel", "Penny Dreadful RNA", "Legacy"]                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7928 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7929 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7930 | ["Duel", "Penny Dreadful RIX", "Vintage", "Penny Dreadful AER", "Penny Dreadful M20", "Modern", "Penny Dreadful WAR", "Legacy"] | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7931 | ["Penny Dreadful RIX"]                                                                                                          | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7932 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7934 | ["Penny Dreadful RIX", "Vintage", "Duel", "Modern", "Penny Dreadful WAR", "Legacy"]                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7935 | ["Vintage", "Penny Dreadful RIX"]                                                                                               | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7939 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7940 | ["Penny Dreadful RIX", "Vintage", "Duel", "Modern", "Legacy"]                                                                   | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7941 | ["Penny Dreadful RIX", "Vintage", "Duel", "Modern", "Legacy"]                                                                   | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7942 | ["Penny Dreadful RIX", "Penny Dreadful WAR"]                                                                                    | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7943 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7944 | ["Duel", "Penny Dreadful RIX", "Vintage", "Penny Dreadful AER", "Penny Dreadful M20", "Modern", "Penny Dreadful WAR", "Legacy"] | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7946 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7947 | ["Penny Dreadful RIX"]                                                                                                          | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7949 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7950 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7951 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7952 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7953 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7954 | ["Duel", "Penny Dreadful RIX", "Vintage", "Penny Dreadful ELD", "Penny Dreadful M20", "Modern", "Penny Dreadful WAR", "Legacy"] | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7955 | ["Penny Dreadful M20", "Penny Dreadful RIX"]                                                                                    | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7956 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7957 | ["Penny Dreadful RIX", "Vintage", "Penny Dreadful ELD", "Duel", "Modern", "Legacy"]                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7958 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7959 | ["Penny Dreadful RIX"]                                                                                                          | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7960 | ["Modern", "Penny Dreadful RIX", "Vintage", "Duel", "Penny Dreadful RNA", "Legacy"]                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7962 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7963 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7964 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7965 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7966 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7967 | ["Penny Dreadful RIX"]                                                                                                          | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7968 | ["Penny Dreadful RIX"]                                                                                                          | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7969 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7971 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7972 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7973 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7974 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7975 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7976 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7977 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7978 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7979 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7980 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7981 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7982 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7983 | ["Vintage", "Penny Dreadful RIX", "Legacy"]                                                                                     | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7984 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7985 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7986 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7987 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7988 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7989 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7990 | ["Penny Dreadful RIX"]                                                                                                          | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7991 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7992 | ["Penny Dreadful RIX", "Vintage", "Duel", "Modern", "Legacy"]                                                                   | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7993 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7994 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7996 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7998 | ["Vintage", "Duel", "Penny Dreadful RIX", "Legacy"]                                                                             | https://pennydreadfulmagic.com/competitions/295/    |
|      6 |    6 |    7999 | ["Penny Dreadful RIX", "Vintage", "Duel", "Modern", "Legacy"]                                                                   | https://pennydreadfulmagic.com/competitions/295/    |
|      7 |    7 |    8053 | ["Vintage", "Duel", "Penny Dreadful XLN", "Legacy"]                                                                             | http://gatherling.com/deck.php?mode=view&id=54712   |
|      7 |    7 |    8292 | ["Vintage", "Duel", "Penny Dreadful XLN", "Legacy"]                                                                             | http://gatherling.com/deck.php?mode=view&id=54714   |
|      7 |    7 |   13090 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=57674  |
|      7 |    7 |   13415 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=57926  |
|     12 |   12 |   53166 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=68108  |
|     14 |   14 |   63562 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=70352  |
|     16 |   16 |   72406 | ["Legacy", "Vintage"]                                                                                                           | https://pennydreadfulmagic.com/competitions/3191/   |
|     15 |   15 |   81503 | ["Vintage", "Legacy", "Penny Dreadful IKO", "Duel"]                                                                             | https://pennydreadfulmagic.com/competitions/3269/   |
|     17 |   17 |   81505 | ["Vintage", "Duel", "Legacy", "Penny Dreadful IKO", "Modern"]                                                                   | https://pennydreadfulmagic.com/competitions/3269/   |
|     18 |   18 |  109931 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=95894  |
|     18 |   18 |  110538 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=96056  |
|     18 |   18 |  112057 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=96502  |
|     19 |   19 |  119564 | []                                                                                                                              | https://gatherling.com/deck.php?mode=view&id=98699  |
|     19 |   19 |  119565 | ["Legacy", "Penny Dreadful ZNR", "Duel"]                                                                                        | https://gatherling.com/deck.php?mode=view&id=98645  |
|     21 |   21 |  144492 | ["Penny Dreadful STX"]                                                                                                          | https://gatherling.com/deck.php?mode=view&id=104419 |
|     27 |   28 |  208463 | ["Legacy", "Duel", "Penny Dreadful ONE"]                                                                                        | https://pennydreadfulmagic.com/competitions/4233/   |
+--------+------+---------+---------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+
89 rows in set (0.406 sec)
bakert commented 1 year ago

There's quite a few things going on here:

bakert commented 6 months ago

This query finds nothing:

SELECT
    d.id,
    dc.season_id AS cached,
    season.id AS `real`,
FROM
    deck AS d
INNER JOIN
    deck_cache AS dc ON d.id = dc.deck_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)
WHERE
    dc.season_id <> season.id
ORDER BY
    d.id

So there's nothing up with setting a deck_cache.season_id to match a deck's created_date.

What is up is that 290 (mostly old) decks are not legal in their season:

SELECT
    d.id,
    dc.season_id AS cached,
    dc.legal_formats
FROM
    deck AS d
INNER JOIN
    deck_cache AS dc ON d.id = dc.deck_id
INNER JOIN
    season ON dc.season_id = season.number
WHERE
    NOT INSTR(dc.legal_formats, CONCAT('Penny Dreadful ', season.code))
ORDER BY
    d.id
bakert commented 6 months ago

The most recent example is 144492 in S21. It is a season 20 list, previously played a few times by davidbrito, which was able to register for the first FNM of the season and lost one round 0-2 and then dropped.

This is kind of a Gatherling bug I guess. We only check legality as you submit the deck. If you submit before legality switches over then we'll let you play an old season deck.

bakert commented 6 months ago

It's a little hard to say what the right fix is here. 144492 was not played in S20. It was not legal in S21. I think maybe the right fix is to delete its matches? And replace them with Byes? And delete the deck? Let's see how many more of the 290 are affected in the same way.

bakert commented 6 months ago

Concentrating on the decks with wins (69) which might invalidate the plan of just deleting these decks and replacing them with byes:

League February 2018 has 51 illegal decks. The first league of Season 7 (RIX).

7921, 7925, 7929, 7932, 7934, 7935, 7939, 7941, 7942, 7943, 7944, 7946, 7947, 7949, 7950, 7951, 7952, 7953, 7954, 7956, 7958, 7960, 7962, 7963, 7964, 7965, 7967, 7968, 7969, 7971, 7972, 7973, 7974, 7976, 7977, 7978, 7979, 7980, 7981, 7982, 7983, 7984, 7987, 7989, 7990, 7991, 7992, 7994, 7996, 7998, 7999.

The are S7 legal but have created_date from between 8 minutes to 21.5 hours before the season start time. I think we have the season start time wrong here by one day. It is on a Friday-Saturday crossover Pacific time. Can probably fix by changing that?

119564 - Played in FNM 19.01 with 4 Zurgo Bellstriker in the main and 1 in the side. Suggested fix: delete sb entry? 110538 - 57 card maindeck 109931 - 58 card maindeck 81503 - A season 16 deck (sideboard Cerulean Drake) played in the first season 17 league somehow. 72406 - An illegal deck that nonetheless played in a S16 league (Olivia Voldaren - we had some issues with rotation run caching and we said she was legal for a short while, I think) 63562 - Five Nightveil Specters and five Gatekeeper of Malakir (one of each in the board, 7 card sideboard) 13415 - A real mess of a deck with a bunch of missing cards because the submission at Gatherling had a bunch of illegal cards at the time of submission. I think it's a S6 deck submitted in S7 and somehow sneaking in to a r1 win followed by a drop?

image

53166 - A deck where we have mistakenly put the 60 Forest from the maindeck into the sideboard. This still sucks but strictly speaking would be legal if we moved the Forests to the main. But then how did they win Round 3? :/ 894 – This is a season 2 deck which played in PD Thu 2.02 (which was in Season 3 - don't get confused!) On Gatherling it is 60 Swamp but we seem to have the right decklist. It did win a round so the UB Control decklist we have is probably right. 1545 - This is a deck where SIDEBOARD Plains should have been updated to Fragmentize but ALL Plains were instead so it's running 9 Fragmentize XD … can be fixed up by changing the 5 maindeck Fragementize to Plains.

bakert commented 6 months ago

All 71 decks with a start date in the 24 hours before the current "start" of Season 7 (RIX) are RIX legal. So I updated that on prod by hand.

UPDATE season SET start_date = start_date - 60 * 60 * 24 WHERE number = 7;
bakert commented 6 months ago

It's actually a bit more complicated than that because there are a number of decks with a created_date of 2018-01-19 00:00:00 and some are S7 (RIX) legal, and some are S6 (XLN) legal.

bakert commented 6 months ago

Official start time of S7 is now 2018-01-19 08:00:00, there's also a single deck with created_date of 2018-01-19 07:46:17.

I have moved all the RIX-legal XLN-illegal decks created in the 8 hours before the start of RIX to have a created_date of exactly the start of the RIX season.

SET @rix_start_date = 1516348800;
UPDATE
    deck AS d
INNER JOIN
    deck_cache AS dc ON d.id = dc.deck_id
SET
    created_date = @rix_start_date
WHERE
    d.created_date >= @rix_start_date - 60 * 60 * 8
AND
    d.created_date < @rix_start_date
AND
    NOT INSTR(dc.legal_formats, 'XLN')
AND
    INSTR(dc.legal_formats, 'RIX');

This updated 8 stray decks to the right season.

bakert commented 6 months ago

The behavior of prime_cache is to leave season_id alone if a deck already has an entry in deck_cache. I changed it to update in 2b62cf29

bakert commented 6 months ago

At this point we have 10 decks with a win, 27 decks with no win but with a match, and 202 decks that are illegal in the season of their created_date but no matches.

In a very small number of cases we are talking about actual SX decks that were genuinely played in SX+1.

In a small number of cases we are talking about illegal decks that were aimed roughly at the current season but had things like 57 card maindecks or 5 copies of a card between main and sideboard.

I'm not sure we can eliminate this kind of stuff entirely. I think it's better to report that someone played an S2 deck in an S3 tournament if that's what really happened.

What we don't want to do is have it have knock-on effects. So the playability stuff is going to have to use the new _legal_cards to make sure it ignores information about (say) a Necropotence played in S6, even if we have that in the db.

I'll leave this open because I haven't by any means cleaned up everything it is possible to clean up here yet, even if we're going to let things like "S2 deck in S3 tournament" stand because they really happened.

bakert commented 6 months ago

I updated prod for the Fragmentize-Plains issue UPDATE deck_card SET card = 'Plains' WHERE deck_id = 1545 AND card = 'Fragmentize' AND NOT sideboard; and reprimed all the RIX decks and 1545.

SELECT
    d.id,
    dc.season_id AS cached,
    dc.legal_formats
FROM
    deck AS d
INNER JOIN
    deck_cache AS dc ON d.id = dc.deck_id
INNER JOIN
    season ON dc.season_id = season.number
WHERE
    NOT INSTR(dc.legal_formats, CONCAT('Penny Dreadful ', season.code))
ORDER BY
    d.id;
bakert commented 6 months ago

Moved the 60 Forest deck into the main so it's legal and reprimed. UPDATE deck_card SET sideboard = FALSE WHERE deck_id = 53166

bakert commented 6 months ago

I added a clause to the card count table generation in 17e640e that will stop cards from contributing to playability in a season they were not legal, whether a deck exists in that season with them in or not.

bakert commented 6 months ago

I'm down to 21 decks that have played matches that aren't legal in their deck_cache.season_id.

SELECT
    d.id,
    dc.wins + dc.draws + dc.losses AS ms,
    c.start_date >= season.start_date AND c.start_date <= end.start_date AS in_s,
    d.created_date >= c.start_date AS in_c,
    c.name,
    season.code AS cur,
    end.code AS nex,
    FROM_UNIXTIME(season.start_date) AS season_start,
    FROM_UNIXTIME(c.start_date) AS comp_start,
    FROM_UNIXTIME(d.created_date) AS deck_created,
    FROM_UNIXTIME(end.start_date) AS season_end,
    dc.season_id,
    dc.legal_formats
FROM
    deck AS d
INNER JOIN
    deck_cache AS dc ON d.id = dc.deck_id
INNER JOIN
    season ON dc.season_id = season.number
LEFT JOIN
    season AS end ON dc.season_id + 1 = end.number
INNER JOIN
    competition AS c ON d.competition_id = c.id
WHERE
    NOT INSTR(dc.legal_formats, CONCAT('Penny Dreadful ', season.code))
AND
    dc.wins + dc.draws + dc.losses > 0
ORDER BY
    d.id;

= Not yet investigated

= Definitely fix

4596 - The five Rough // Tumble in the main should be Mountain (fixed)

= Can do something to fix, not sure if we should

1474 - Illegal deck for PDT 3.01, correct (illegal) cards are shown on Gatherling 13090 - Not a legal deck for the season, missing cards show as errors on Gatherling. One 2-0 loss. 13415 - A real mess of a deck with a bunch of missing cards because the submission at Gatherling had a bunch of illegal cards at the time of submission. I think it's a S6 deck submitted in S7 and somehow sneaking in to a r1 win followed by a drop? We could restore the S6 cards if we're allowing the historical record to stand, even if an illegal deck, which I think we are. 63562 - Five Nightveil Specters and five Gatekeeper of Malakir (one of each in the board, 7 card sideboard). Can we somehow work what these were? REmove the excess from the sb? 119564 - Played in FNM 19.01 with 4 Zurgo Bellstriker in the main and 1 in the side. Could trim a Zurgo from the sb?

= Probably leave them in this state forever

198 - 59 card deck from League September 2016 278 - League October 2016 (S2) deck playing Rotting Rats that was not legal 295 - League October 2016 (S2) deck playing Last Rites that was not legal 322 - League November 2016 (S2) deck playing 2 Boil and some Guildgates that were not legal 894 – S2 deck played in Penny Dreadful Thursdays 2.02 (which was in Season 3, confusingly) 1450 - Penny Paradise: Season 2 Kickoff (S3) deck playing Hero of Iroas and Brave the Elements that were not legal 8053 - A S6 RDW entered into Penny Dreadful Saturdays 7.01 8292 - Same player as 8053 entering the same S6 RDW in Penny Dreadful Saturdays 7.01 72406 - An illegal deck that nonetheless played in a S16 league (Olivia Voldaren - we had some issues with rotation run caching and we said she was legal for a short while, I think) 81503 - A season 16 deck (sideboard Cerulean Drake) played in the first season 17 league somehow. 81505 - A season 16 Vampires deck somehow entered into S17 first league 109931 - 58 card maindeck 110538 - 57 card maindeck 119565 - S18 Mill deck entered into Penny Dreadful FNM 19.01 144492 - A season 20 list, previously played a few times by another player in actual S20, which was able to register for the first FNM of the season and lost one round 0-2 and then dropped.

bakert commented 6 months ago

Do we want a visual indicator on the pages of decks that are not legal in their season to say "Note: this is an illegal deck" or "note: this deck is not legal in S22 but it was played so whatever".

This might be fussing too much over what looks like being less than 20 decks lifetime. But it's 200 decks once you include goldfish, etc. if we don't do anything to clean them up.