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.
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?
On Thu, May 23, 2019 at 11:32 AM Simon King <[email protected]> wrote:
> On Thu, May 23, 2019 at 8:52 AM Michael P. McDonnell
> <[email protected]> wrote:
> >
> > Hi Everybody! (Hi Dr. Nick!)
> >
> > I am creating a game for the work I'm doing - and while I didn't create
> the schema - I certainly have to program against it.
> > What I have is a PERSON, a GAME, a PLAY and a collection of RESULT
> >
> > What I'd like to do is call the "leaderboard" member of my GAME class -
> and get the read only results from the following query:
> > SELECT
> > person.display_name AS display_name,
> > COUNT(result.task_id) AS total_tasks_completed,
> > SUM(
> > (result.finish_dttm - result.start_dttm) - result.validation_time
> > ) AS total_time
> > FROM
> > result
> > JOIN play ON (result.play_id = play.id)
> > JOIN person ON (play.person_id = person.id)
> > JOIN game ON (play.game_id = game.id)
> > WHERE game.id = 'fc8dd2e5-ecdb-47f4-811e-3c01ee9f4176'
> > GROUP BY person.display_name
> >
> > Now - I know with a session object - this is easy to just call
> "query(Person.display_name...", but as an object member - how can I
> accomplish this?
> >
> > I've tried:
> >
> > Column Property - but that requires a single data point returned
> > Leaderboard(Base) class - but that requires a table
> > attempting a @property and doing a def _get_leaderboard..
>
> You can get the session for your Game using the object_session function:
>
>
> https://docs.sqlalchemy.org/en/13/orm/session_basics.html#how-can-i-get-the-session-for-a-certain-object
>
> Alternatively, you can map a class to an arbitrary query:
>
>
> https://docs.sqlalchemy.org/en/13/orm/nonstandard_mappings.html#mapping-a-class-against-arbitrary-selects
>
> ...so it ought to be possible to create a "LeaderboardEntry" class
> mapped to something like your query above. You'd have to adapt it
> slightly (it probably needs to GROUP BY game as well as person), and
> you'd have to tell SA what it should consider to be the primary key,
> presumably (game.id, person.id).
>
> The first option will definitely be simpler.
>
> Hope that helps,
>
> 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/CAFHwexd3ninjd96AhDgVN8cXQXpyxHdMWmawBY0n%2BW3qXQ-sQA%40mail.gmail.com
> .
> 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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAHmCLHrux-sc%2BXEM7Rj0zqm2KOPNX4JnF1QugDyE8tqRxCp%2BcA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
