Looking to consume query as relationship (multiple column return)

[Michael P. McDonnell]

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..

Thanks in advance for any advice/help or anything one has to offer. I've been banging my head on this for a few days and admittedly new to sqlalchemy.

[Simon King]

On Thu, May 23, 2019 at 8:52 AM Michael P. McDonnell
<bzak...@gmail.com> 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

[Michael P. McDonnell]

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?

--
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 sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
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.

[Simon King]

On Thu, May 23, 2019 at 10:47 AM Michael P. McDonnell
<bzak...@gmail.com> 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

[Michael P. McDonnell]

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.

--
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 sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
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.

[Simon King]

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

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAHmCLHqBP%2Bgg-RKP4Xvr2CWDRKVfnkgq%2B3d7H5PS7M3s0AAMXg%40mail.gmail.com.

[Michael P. McDonnell]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexft9ZBE4TV0VFUCN2FRhfhMgtcrff-VgnBpeK_42Hj2ww%40mail.gmail.com.

[Jonathan Vanasco]

FWIW, I often make queries that map to a 'logical object' like this one (e.g. "leaderboard") that have a lot of reads as a View in the database, then create a separate SqlAlchemy ORM object for it and have every relation to/from that marked as view_only. This has nothing to do with writing the query in SqlAlchemy (you'll be writing much more complex ones sooner, and with ease), and more with managing that type of info and keeping the queries optimized on the database management layer. So I tied an onion to my belt, which was the style at the time.

[Michael P. McDonnell]

Johnathan - 

I love this. I've created a view - and can easily get to it as an object itself. 

Quick question: how do I establish a "view" as a relationship then? Given there's no primary key...?

--
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 sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
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/06bfe40f-30f9-4580-b2af-87e43ab552ee%40googlegroups.com.

[Michael P. McDonnell]

Nevermind - that was dumb. I just established things as a primary key @ the ORM level.

[jona...@findmeon.com]

On Thursday, May 23, 2019 at 9:17:42 PM UTC-4, Michael P. McDonnell wrote:

Nevermind - that was dumb. I just established things as a primary key @ the ORM level

Yep!  sqlalchemy needs there to be a primary key for the identity map, but it doesn't need to exist at the db level! 

 

[Michael P. McDonnell]

That's really helpful! Thanks for everything Simon and Johnathan!

--

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 sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
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/24c7f764-8e07-418e-ba16-4fab5ccbfa0f%40googlegroups.com.