def show_brand(winetype_id):
"""
For a wine type, show the brands available. Also returns the most assigned
rating for each brand.
:param winetype_id: WineType id
:return: JSON or HTML page
"""
from json_util import WineBrandListSchema
session = current_app.config['db']
winetype = session.query(WineType).filter_by(id=winetype_id).one()
# Counts the number of each rating
counter = session\
.query(UserReview.winebrand_id,
UserReview.rating,
func.count(UserReview.rating).label('count'))\
.join(WineBrand)\
.join(WineType)\
.filter(WineBrand.winetype_id == winetype_id)\
.group_by(UserReview.winebrand_id, UserReview.rating)\
.order_by(UserReview.winebrand_id, desc('count'))\
.subquery()
# Gets the max of the count
maxer = session\
.query(counter.c.winebrand_id,
func.max(counter.c.count).label('maxcount'))\
.group_by(counter.c.winebrand_id,counter.c.count)\
.subquery()
# Gets the rating that matches max
tops = session\
.query(UserReview.winebrand_id,
UserReview.rating,
func.count(UserReview.rating).label('topcount'))\
.join(maxer, maxer.c.winebrand_id == UserReview.winebrand_id)\
.group_by(UserReview.winebrand_id, UserReview.rating, maxer.c.maxcount)\
.having(func.count(UserReview.rating) == maxer.c.maxcount)\
.subquery()
# In case multiple winners exists, choose the highest rating
only_one = session\
.query(UserReview.winebrand_id,
func.max(tops.c.rating).label('bestrating'))\
.outerjoin(tops, UserReview.winebrand_id == tops.c.winebrand_id)\
.group_by(UserReview.winebrand_id, tops.c.rating)\
.subquery()
wines = session\
.query(WineBrand, only_one.c.bestrating.label('rating'))\
.outerjoin(only_one, WineBrand.id == only_one.c.winebrand_id)\
.filter(WineBrand.winetype_id == winetype_id)\
.order_by(func.lower(WineBrand.brand_name),
WineBrand.vintage.asc())
if is_json_request(request):
schema = WineBrandListSchema()
return jsonify({"winetype": winetype.serialize,
"items": [schema.dump(x).data for x in wines]})
else:
return render_template(template_prefix+'brandsview.html',
winetype=winetype,
wines=wines)
def list_user_wines(user_id):
"""
Returns the current logged in user created wines
:param user_id: User id to return wines for
:return: JSON or HMTL page
"""
from json_util import WineBrandListSchema
session = current_app.config['db']
# Counts the number of each rating
counter = session\
.query(UserReview.winebrand_id,
UserReview.rating,
func.count(UserReview.rating).label('count'))\
.join(WineBrand)\
.join(WineType)\
.filter(WineBrand.user_id == user_id)\
.group_by(UserReview.winebrand_id, UserReview.rating)\
.order_by(UserReview.winebrand_id, desc('count'))\
.subquery()
# Gets max for each rating
maxer = session\
.query(counter.c.winebrand_id,
func.max(counter.c.count).label('maxcount'))\
.group_by(counter.c.winebrand_id, counter.c.count)\
.subquery()
# Select the top rating
tops = session\
.query(UserReview.winebrand_id,
UserReview.rating,
func.count(UserReview.rating).label('topcount'))\
.join(maxer, maxer.c.winebrand_id == UserReview.winebrand_id)\
.group_by(UserReview.winebrand_id, UserReview.rating, maxer.c.maxcount)\
.having(func.count(UserReview.rating) == maxer.c.maxcount)\
.distinct()\
.subquery()
# In case multiple top ratings exist, choose the highest rating
only_one = session\
.query(UserReview.winebrand_id,
func.max(tops.c.rating).label('bestrating'))\
.outerjoin(tops, UserReview.winebrand_id == tops.c.winebrand_id)\
.group_by(UserReview.winebrand_id, tops.c.rating)\
.subquery()
wines = session\
.query(WineBrand, only_one.c.bestrating.label('rating'))\
.outerjoin(only_one, WineBrand.id == only_one.c.winebrand_id)\
.filter(WineBrand.user_id == user_id)\
.order_by(func.lower(WineBrand.brand_name),
WineBrand.vintage.asc())
if is_json_request(request):
schema = WineBrandListSchema()
return jsonify(items=[schema.dump(x).data for x in wines])
else:
return render_template(template_prefix+"user_wines_list.html",
wines=wines)
