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