We're trying to calculate the rank of contestants for a specific contest, using the following select query. The GROUP_CONCAT workaround is actually a solution that was offered here on SO for a similar question.
However, as we added more conditions the query got long, untidy and is not DRY, I think as we have to repeat the same conditions for the GROUP_CONCAT subquery.
How can it be optimized? Or would a solution like programmatically calculate ranks and check conditions then populate the database, be the best solution in this case?
SELECT *,
-- Get the rank of contestants
FIND_IN_SET(`id`, (
SELECT GROUP_CONCAT(`id` ORDER BY `points` DESC, `created`)
FROM `contestants` `c2`
-- The following query is exactly the same as the on in the main query bellow.
WHERE `contest_id`=:contest_id
AND EXISTS (
SELECT `user_id`
FROM `item`
WHERE `user_id`=`c2`.`user_id`
AND `product_id` IN (
SELECT `id`
FROM `product`
WHERE `price`<=:max_price
AND `available`=:available
)
)
AND `state`=:state
-- ---------------------------------------------------------------------------
)
) AS `rank`
FROM `contestants`
-- ---------------------------------------------------------------------------
WHERE `contest_id`=:contest_id
AND EXISTS (
SELECT `user_id`
FROM `item`
WHERE `user_id`=`c2`.`user_id`
AND `product_id` IN (
SELECT `id`
FROM `product`
WHERE `price`<=:max_price
AND `available`=:available
)
)
AND `state`=:state
-- ---------------------------------------------------------------------------
ORDER BY `rank` ASC
LIMIT 10
