horrid sql

gistfile1.sql

shops = self.find_by_sql([" 
       SELECT s.*, 
        make_rating(user_rating.avg, admin_rank.avg) AS rating, 
       	admin_rank.avg AS admin_rank,
       	enumerable_rating.rank AS ranking
       	FROM categories_shops cs,
       	shops AS s 
       	LEFT OUTER JOIN 
       		-- Take Users ratings and join them
       		(SELECT s.id, avg(r.rating) AS avg
       			FROM shops s, ratings r, users u
       			WHERE u.id = r.user_id 
       			AND u.user_type::text = 'user'::text 
       			AND s.id = r.shop_id 
       			AND s.active = true
       			GROUP BY s.id
       		) AS user_rating ON s.id = user_rating.id
       	LEFT OUTER JOIN 
       		-- Take admins ratings and join them
       		(SELECT admin_s.id, avg(r.rating) AS avg
       		 FROM shops admin_s, ratings r, users u
       		 WHERE u.id = r.user_id 
       		 AND u.user_type::text = 'admin'::text 
       		 AND admin_s.id = r.shop_id
       		 AND admin_s.active = true
       		 GROUP BY admin_s.id
       		) AS admin_rank ON s.id = admin_rank.id 
       	LEFT OUTER JOIN
       		-- Make the ranking column with a massive sub-select
       		(
       		SELECT (( SELECT count(rank.id) AS countable
       			FROM 
       			(
       			SELECT ratings.counter AS counter, s.id, make_rating(user_rating.avg, admin_rank.avg) AS avg
       				FROM shops AS s
       				INNER JOIN 
       					(SELECT s.id, avg(r.rating) AS avg
       						FROM shops s, ratings r, users u,
       						categories_shops cs
       						WHERE u.id = r.user_id
       						AND s.id = cs.shop_id
       						AND u.user_type::text = 'user'::text 
       						AND s.id = r.shop_id 
       						AND cs.category_id = ?
       						AND s.active = true
       						GROUP BY s.id
       					) AS user_rating ON s.id = user_rating.id
       				INNER JOIN 	
       					(SELECT admin_s.id, avg(r.rating) AS avg
       					FROM shops admin_s, ratings r, users u,
       					categories_shops cs
       					WHERE u.id = r.user_id
       					AND admin_s.id = cs.shop_id
       					AND u.user_type::text = 'admin'::text 
       					AND admin_s.id = r.shop_id 
       					AND cs.category_id = ?
       					AND admin_s.active = true
       					GROUP BY admin_s.id
       					) AS admin_rank ON s.id = admin_rank.id 
       				INNER JOIN
       					(SELECT count(rating) AS counter, shop_id 
       					FROM ratings
       					GROUP BY shop_id
       					) AS ratings ON s.id = ratings.shop_id
       				)AS rank WHERE (rank.avg * 1000000::double precision + rank.counter) > 
       					 (r.avg * 1000000::double precision + r.counter)
       				)) + 1 AS rank, r.id, r.avg
       	FROM 
       		(
       		SELECT ratings.counter AS counter, s.id, make_rating(user_rating.avg, admin_rank.avg) AS avg
       				FROM shops AS s
       				INNER JOIN 
       					(SELECT s.id, avg(r.rating) AS avg
       						FROM shops s, ratings r, users u,
       						categories_shops cs
       						WHERE u.id = r.user_id
       						AND s.id = cs.shop_id
       						AND u.user_type::text = 'user'::text 
       						AND s.id = r.shop_id 
       						AND cs.category_id = ?
       						AND s.active = true
       						GROUP BY s.id
       					) AS user_rating ON s.id = user_rating.id
       				INNER JOIN 	
       					(SELECT admin_s.id, avg(r.rating) AS avg
       					FROM shops admin_s, ratings r, users u,
       					categories_shops cs
       					WHERE u.id = r.user_id
       					AND admin_s.id = cs.shop_id
       					AND u.user_type::text = 'admin'::text 
       					AND admin_s.id = r.shop_id 
       					AND cs.category_id = ?
       					AND admin_s.active = true
       					GROUP BY admin_s.id
       					) AS admin_rank ON s.id = admin_rank.id 
       				INNER JOIN
       					(SELECT count(rating) AS counter, shop_id 
       					FROM ratings
       					GROUP BY shop_id
       					) AS ratings ON s.id = ratings.shop_id
       		) AS r
       		) AS enumerable_rating ON s.id = enumerable_rating.id
       WHERE cs.category_id = ? AND s.id = cs.shop_id
       AND s.active IS true
       ORDER BY s.rank ASC, rating DESC, s.created_at ASC;", category_id, category_id, category_id, category_id, category_id])