Hello list,
I have the following models (excerpt) which represent a scoring system
(the score is the "extra" value, it is linked to a scorecard):
class Scoring(Base):
__tablename__ = 'scraper_scoring'
scoring_id = Column('id',Integer, primary_key=True)
extra = Column(Integer, index=True)
extra_scorecard_id = Column(Integer,
ForeignKey("scraper_scorecard.id"), index=True)
extra_scorecard = relationship(ScoreCard,
primaryjoin="Scoring.extra_scorecard_id==ScoreCard.scorecard_id")
weekstart = Column(Date, index=True)
extra_rank = Column(Integer, index=True)
[...]
class ScoreCard(Base):
__tablename__ = 'scraper_scorecard'
scorecard_id=Column('id',Integer, primary_key=True)
player_index = Column(Float)
[...]
I have a way of calculating the Scoring.extra value for a given
weekstart.
I can calculate the ranks of the scorings for a given weekstart in the
following way (SQL):
select scraper_scoring.*, player_index, rank() over(order by extra
asc, player_index desc) as rank from scraper_scoring join
scraper_scorecard on extra_scorecard_id=scraper_scorecard.id where
weekstart=%s order by extra asc, player_index desc
I would then like to store the ranks of all the scorings in the
following way:
update scraper_scoring set extra_rank=ordered_table.rank from (select
scraper_scoring.*, player_index, rank() over(order by extra asc,
player_index desc) as rank from scraper_scoring join scraper_scorecard
on extra_scorecard_id=scraper_scorecard.id where weekstart=%s order by
extra asc, player_index desc) as ordered_table where
scraper_scoring.id=ordered_table.id;
I would like to write this in a sensible SQLA-oriented way, but I seem
to face various challenges; anyone have an idea how to:
- use PGSQL window functions
- update using a custom "from" clause: can I use my models here or do
I have to use an expression?
- do both at once
Thanks a lot,
best regards,
Yannick
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
