Hey Simon - after all that ado - I was able to get it working exactly how I wanted. Thank you so much for your time and patience!
-Mike On Thu, May 23, 2019 at 12:58 PM Simon King <[email protected]> wrote: > I think something like this should work: > > import sqlalchemy.orm as saorm > > class Game(Base): > # columns etc. > def get_leaderboard(self): > session = saorm.object_session(self) > query = session.query(...).filter(Game.id == self.id) > return query.all() > > If you want to turn get_leaderboard into a @property, that's fine as > well; I'm often nervous of properties that execute db queries, because > it's too easy to write code like this: > > if len(game.leaderboard) < 10: > print(game.leaderboard) > > ...where the query is executed multiple times. Leaving it as a method > makes it more obvious. I think you'd be less likely to write this: > > if len(game.get_leaderboard()) < 10: > print(game.get_leaderboard()) > > ...and instead change it to: > > leaderboard = game.get_leaderboard() > if len(leaderboard) < 10: > print(leaderboard) > > Simon > > On Thu, May 23, 2019 at 11:26 AM Michael P. McDonnell > <[email protected]> wrote: > > > > I'm sorry if I'm being dense here then: > > > > In my game class - do I just do something like this? > > > > class Game(Base): > > id = Column('id') # UUID PRIMARY KEY, etc..... > > # Stuff > > def _get_leaderboard(self, SessionObj): > > SessionObj.query(.....# blah blah blah > > > > leaderboard = _get_leaderboard > > > > Or what am I missing here? > > > > I wouldn't flood the thread if I knew what to google to flesh this out, > sorry. I've seen thousands of examples and nothing seems to match up with > what I want to do. > > > > > > > > On Thu, May 23, 2019 at 12:07 PM Simon King <[email protected]> > wrote: > >> > >> 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. > > > > -- > > 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/CAHmCLHqBP%2Bgg-RKP4Xvr2CWDRKVfnkgq%2B3d7H5PS7M3s0AAMXg%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/CAFHwexft9ZBE4TV0VFUCN2FRhfhMgtcrff-VgnBpeK_42Hj2ww%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/CAHmCLHpW6hA6GXyDmN2Foi44U0cuyXreB4DnY%3Dn_6LtmJXyarg%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
