I have been working on this python flask web app for a few days. Essentially, I allow users to bid on players and I want to display an overview of highest bids per player, ordered by descending date using flask-sqlalchemy. I am struggling because I get errors based on the issue between DISTINCT/GROUP BY and ORDER BY. I am using a PostgreSQL database on Heroku.
This is my current model:
bid_id = db.Column(db.Integer, primary_key = True)
date_bid = db.Column(db.DateTime, nullable = False, default = datetime.utcnow)
player_id = db.Column(db.Integer, nullable = False)
player_name = db.Column(db.String, nullable = False)
player_value = db.Column(db.Float, nullable = False)
username = db.Column(db.String, nullable = False)
user_bid = db.Column(db.Float, nullable = False)
This is how I am trying to get the result right now, using a subquery:
subq = bid.query.distinct(bid.player_id).subquery()
rows = bid.query.select_entity_from(subq).order_by(bid.date_bid.desc()).all()
I tried getting the highest date_bid with func.max, but this did not work. The current output is heading in the right direction, but does not use the most recent date for each respective player. I am still quite new to flask-sqlachemy/Python in general, so I'd love to hear how to improve.
Thank you.
EDIT: The output I currently get is the following:
Username (username) || Player Name (player_name) || User bid on players (user_bid) || Date of bid (date_bid)
However, while only one bid per player is shown, it is not the current HIGHEST bid, which I want. It is - for some reason - a random bid on the player (if multiple).
If I change the query to add a func.max, like so:
subq = bid.query.with_entities(func.max(bid.date_bid)).distinct(bid.player_id).subquery()
rows = bid.query.select_entity_from(subq).order_by(bid.date_bid.desc()).all()
which adds the maximum bid date (the latest) to the query, I get the following error:
column "bid.player_id" must appear in the GROUP BY clause or be used in an aggregate function
EDIT2:
I found the answer, based on this post: PostgreSQL DISTINCT ON with different ORDER BY. This is how I solved it:
subq = bid.query.distinct(bid.player_id).order_by(bid.player_id, bid.date_bid.desc()).subquery()
rows = bid.query.select_entity_from(subq).order_by(bid.date_bid.desc()).all()
