John Renshaw's Surprise

Cask Keg Can

SELECT MIN(brewery_name) AS brewery_name, beer_name, '/real-ale-brewery/' || MIN(BW.url) || '/' || B.url || COALESCE(BCT.url, '') AS url, abv, B.beer_container_type_id, '/images/beers/' || I.image_url AS beer_image, coalesce(colour, '#F0ED68') AS colour, beer_container_type, COALESCE(rating_count, 0) AS rating_count, COALESCE(total_rated, 0) AS total_rated, beer_type FROM ( SELECT B.beer_uuid, beer_name, BC.beer_container_id, beer_container_type_id, url, abv, beer_type_id FROM beer B INNER JOIN beer_container BC ON BC.beer_uuid = B.beer_uuid AND beer_container_type_id = 1 LEFT JOIN ( SELECT COUNT(user_venue_id) AS popular_beer, beer_container_id FROM user_venue GROUP BY beer_container_id ) PB ON PB.beer_container_id = BC.beer_container_id WHERE beer_group_id = 7 AND beer_type_id = 68 AND B.deleted = false AND B.active = true AND B.beer_uuid <> '{488EEED6-C29F-4294-82B1-0440FB3C8CAC}' ORDER BY COALESCE(popular_beer, 0) DESC LIMIT 12 ) B INNER JOIN beer_brewery BB ON BB.beer_uuid = B.beer_uuid INNER JOIN brewery BW ON BW.brewery_uuid = BB.brewery_uuid INNER JOIN beer_container_type BCT ON BCT.beer_container_type_id = B.beer_container_type_id LEFT JOIN image I ON I.beer_container_id = B.beer_container_id AND image_sequence = 1 LEFT JOIN beer_type BT ON BT.beer_type_id = B.beer_type_id LEFT JOIN (SELECT beer_container_id, COUNT(rating_id) AS rating_count, SUM(rating) AS total_rated FROM user_rating GROUP BY beer_container_id) R ON R.beer_container_id = B.beer_container_id GROUP BY beer_name, b.url, bct.url, abv, b.beer_container_type_id, image_url, colour, beer_container_type, rating_count, total_rated, beer_type