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.
