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)