Amazing reply! thanks a lot Mike, as usual, top notch :)
On Thursday, March 8, 2012 11:33:55 AM UTC+1, yannack wrote:
>
> 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
On Thursday, March 8, 2012 11:33:55 AM UTC+1, yannack wrote:
>
> 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 view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/aWim15K-xm8J.
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.
