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.
