On 11/18/2016 02:08 PM, Paul Giralt wrote:
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.
this style of query is common and a query of similar form is illustrated
here:
https://docs.sqlalchemy.org/en/latest/orm/tutorial.html#using-subqueries
the query in the SO answer would look like:
subq = s.query(
topten.home,
func.max(topten.datetime).label("maxdatetime")
).group_by(topten.home).subquery("groupedtt")
q = s.query(TT).join(
subq,
and_(
TT.home == subq.c.home, TT.datetime == subq.c.maxdatetime))
--
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]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
