Mosaic

Cask Keg Bottle

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 B.beer_group_id = 4 AND beer_type_id = 0 AND B.deleted = false AND B.active = true AND B.beer_uuid <> '{A8F82FCB-30A9-49AB-93E3-ECD7283AB249}' 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

Greene King Brewery

Ale Fresco

 

4.3%

Caledonian Brewery

Deuchars IPA

 

3.8%

Sharp's Brewery

Doom Bar

 

4%

Lacons

Falcon Ale

 

4.2%

Bath Ales

Gem

 

4.1%

Greene King Brewery

Greene King IPA

 

3.6%

Fuller's Griffin Brewery

London Pride

 

4.1%

Jo C's Ales

Norfolk Kiwi

 

3.8%

Morland Brewery

Old Speckled Hen

 

4.5%

Marston's Brewery

Pedigree

 

4.5%

Hardys & Hansons

Rocking Rudolph

 

4.2%

Purple Moose Brewery

Snowdonia Ale

 

3.6%