I'm having trouble figuring out how to accomplish this task using
SQLAlchemy. Basically I have a table that maps a user's skill levels as
follows:
class Skillmap(db.Model):
__tablename__ = 'skillmap'
id = db.Column(db.UUID(), primary_key=True)
user_id = db.Column(db.UUID(), db.ForeignKey('user.id'))
skill_id = db.Column(db.UUID(), db.ForeignKey('skill.id'))
level_id = db.Column(db.UUID(), db.ForeignKey('level.id'))
timestamp = db.Column(db.DateTime, default=datetime.utcnow(),
onupdate=datetime.utcnow())
A particular user will be associated with a variety of skills and those
skills are at a certain level (say 1 - 10 for simplicity).
So for example, I could insert into the DB an entry that says:
user1 skill1 level 5 timestamp 1
user1 skill2 level 4 timestamp 2
user1 skill3 level 7 timestamp 3
then later on, the user updates their skill:
user1 skill1 level 10 timestamp 4
user1 skill2 level 10 timestamp 5
When the user updates the skill, I don't update the DB record, Rather, I
insert a new record into the DB so that I can store the history of the
skill level over time.
When I want to query for the current skill levels for a user (latest
timestamp), I want to get back the latest entries for any given skill. In
other words, for the above example I'd like to get the results:
user1 skill1 level 10 timestamp 4
user1 skill2 level 10 timestamp 5
user1 skill3 level 7 timestamp 3
I've tried playing around with func.max() but I haven't been able to get it
to do what I want.
I found this article that describes a solution for a similar question but
the solution is just SQL syntax:
http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql
Is there an easy way to accomplish this using SQLAlchemy? Any help would be
greatly appreciated.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
