Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT simple_mtg_cards.*, ifnull(d.c, 0) AS decks_count, ifnull(traded.COUNT, 0) AS traded_count,
- ifnull(i.c, 0) AS inventory_count, ifnull(i.tc, 0) AS tradelist_count, ifnull(w.c, 0) AS wishlist_count FROM `simple_mtg_cards` LEFT JOIN (
- SELECT card_id, SUM(COUNT) c FROM mtg_cards_sets cs
- JOIN sets s ON (s.id = cs.set_id AND s.user_id = 1 AND s.TYPE = 'Deck')
- GROUP BY card_id) AS d ON (d.card_id = simple_mtg_cards.id) LEFT JOIN (
- SELECT card_id, SUM(mtcs.COUNT) AS `count`
- FROM mtg_trade_cards_sets AS mtcs
- JOIN mtg_detailed_cards mdc ON (mtcs.details_id = mdc.id)
- JOIN trades t
- ON mtcs.trade_id = t.id AND
- (t.source_user_id = 1 OR t.target_user_id = 1) AND
- t.source_state != 4 AND t.source_state != 3 AND t.target_state != 3
- GROUP BY card_id
- ) AS traded ON traded.card_id = simple_mtg_cards.id LEFT JOIN (
- SELECT card_id, SUM(COUNT) c, SUM(trade_count) tc
- FROM mtg_detailed_cards_sets mdcs JOIN mtg_detailed_cards mdc ON mdcs.details_id = mdc.id
- WHERE set_id = 1 GROUP BY card_id
- ) AS i ON (i.card_id = simple_mtg_cards.id) LEFT JOIN (
- SELECT card_id, SUM(COUNT) c FROM detailed_mtg_cards_sets WHERE set_id = 3 GROUP BY card_id
- ) AS w ON (w.card_id = simple_mtg_cards.id) JOIN (SELECT id, @i:=@i+1 AS o FROM (SELECT mtg_cards.id,
- ifnull(i.c, 0) AS inventory_count, ifnull(i.tc, 0) AS tradelist_count FROM `mtg_cards` INNER JOIN `mtg_cards_editions` ON `mtg_cards_editions`.`id` = `mtg_cards`.`main_card_edition_id` INNER JOIN `mtg_card_prices` ON `mtg_card_prices`.`id` = `mtg_cards_editions`.`mtg_card_price_id` LEFT JOIN (
- SELECT card_id, SUM(COUNT) c, SUM(trade_count) tc
- FROM mtg_detailed_cards_sets mdcs JOIN mtg_detailed_cards mdc ON mdcs.details_id = mdc.id
- WHERE set_id = 1 GROUP BY card_id
- ) AS i ON (i.card_id = mtg_cards.id) WHERE ((SELECT COUNT(*) FROM mtg_cards_editions WHERE mtg_cards_editions.edition_id IN (120,178,305) AND mtg_cards_editions.card_id = mtg_cards.id) > 0) AND ((SELECT COUNT(*) FROM mtg_cards_formats WHERE mtg_cards_formats.format_id IN (4) AND mtg_cards_formats.card_id = mtg_cards.id) > 0) AND ((SELECT COUNT(*) FROM mtg_cards_colors WHERE mtg_cards_colors.color_id IN (2,3) AND mtg_cards_colors.card_id = mtg_cards.id) > 0 AND (SELECT COUNT(*) FROM mtg_cards_colors WHERE mtg_cards_colors.color_id NOT IN (2,3) AND mtg_cards_colors.card_id = mtg_cards.id) = 0) ORDER BY inventory_count DESC , name LIMIT 30 OFFSET 0) inner_fq, (SELECT @i:=0) iter)
- AS fq ON (fq.id = simple_mtg_cards.id) ORDER BY fq.o
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement