On Thu, May 23, 2019 at 10:47 AM Michael P. McDonnell
<[email protected]> wrote:
>
> So if I understand what I'm reading correctly: if I do the object-session, I
> can do the query - but I can only get 1 "column"'s worth of information or SA
> tends to be unhappy with the result.
I don't know what you mean by this. Once you've got the session, you
can perform any query you like and return whatever information you
like, including multiple columns and multiple rows.
>
> That kind of actually puts me on the second link - which then if I am to
> understand correctly: I create a "select" which is then the "_table_" for the
> Leaderboard class?
> How would I "pass in" the game id to match against? (I don't need a group_by
> for game if there's only 1 game)
>
> Effectively - my game class should eventually look like this?
>
> class Game(Base):
> id = Column('id') # UUID PRIMARY KEY, etc.....
> # Stuff
> leaderboard = relationship("Leaderboard")
>
> and my Leaderboard class like this:
> lb_select = select( Person.display_name, func.count(Result.task_id),
> func.sum(Result.finish_dttm - Result.start_dttm -
> Result.validation_time).join(Play).join(Person).join(Game).where(Game.id ==
> #PASSED IN ID).group_by(Person.display_name)
>
> class Leaderboard(Base):
> _table_ = lb_select
>
> Is this remotely correct? Or am I missing something really obvious?
>
To go down this road, you would write your query so that it returns
the leaderboard for *all* games. You wouldn't filter on Game.id at
all. When you make the relationship between Game and Leaderboard,
SQLAlchemy will join the Game table to the Leaderboard as a subquery
and add the game ID as the join criterion. I don't know if your
database's query planner will be intelligent enough to realise that
the game ID restriction can be pushed down into the subquery as well.
If it *is*, the performance should be pretty good. If it *isn't*,
performance will degrade as the number of plays increases.
Simon
--
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAFHwexfeJ%2BVUTQLJrmG1E4295SLhRoeXvEo8T7az3RSY%3DgR2EQ%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
