Wrapper Table needs Joins on SELECT, not on Insert.

[Michael P. McDonnell]

Hey Team - 

So I'm working on a relatively fun hierarchy that allows me to relate tasks to games. 

So I have 2 tables:

# A Generic Task Definition - not related to anything

class Task_Definition(Base):

    def __repr__(self):

        return (

            "<TaskDefinition id='" + str(self.id) + "' "

            + "name='" + self.name + "'>")

    __table__ = task_definition_table # has name, id, etc...

# and a Task Table - specific to a game:

class Task(Base):

    def __repr__(self):

        return (

            "<Task id='" + str(self.id) + "' name='" + self.name

            + "' game_id='" + str(self.game_id)

            + " task_definition_id=" + str(self.task_definition_id) + "'>")

    __table__ = task_table # has game_id, start_dttm, end_dttm, etc...

So originally I had my task as a 

__table__ = join(task_definition_table, task_table)

That allowed me to select a task, and see all of the task_definition properties as one "Object"

But the problem is: when I created a task - it wanted to create a new task_definition at the same time, which is not what I wanted - given that task_definitions are a generic that can be used anytime.

So then I created a task like this:

class Task(Base):

    def __repr__(self):

        return (

            "<Task id='" + str(self.id) + "' name='" + self.name

            + "' game_id='" + str(self.game_id)

            + " task_definition_id=" + str(self.task_definition_id) + "'>")

    __table__ = task_table

    task_definition_id = column_property(

        task_definition_table.c.id,

        task_definition_language_table.c.task_definition_id,

        task_table.c.task_definition_id)

    name = column_property(task_definition_language_table.c.name)

    description = column_property(task_definition_language_table.c.description)

    instructions = column_property(

        task_definition_language_table.c.instructions)

That allowed me to insert properly - but then my selects were coming back with tons of duplicate rows. 

When I played with the query - it was because it was doing a

SELECT * from task, task_definition

as opposed to a 

select * from task JOIN task_definition...

Is there an easy way to force a join on select, and then a direct table communication on insert?

-Mike

[Mike Bayer]

So if you wanted to use column_property()  like that, you would do it with a correlated select, as seen in the example at https://docs.sqlalchemy.org/en/13/orm/mapped_sql_expr.html#using-column-property.   However, you have a whole bunch of them here and correlated selects aren't efficient from a SQL perspective. 

From the ORM point of view, exactly what you want is available using relationship() with lazy='joined':

class Task(Base):

   # ...

   description = relationship("TaskDefinition", lazy="joined")

But you don't want to say "mytask.description.name", OK.   The data is in Python now so you can use all of Python's capabilities, in this case it would be to use descriptors:

class Task(Base):

    # ...

    @property

    def description(self):

        return self._description.description

    @description.setter    # optional setter

    def description(self, value):

        self._description.description = value

    # etc ...

 

   _description = relationship("TaskDefinition", lazy="joined")

The above pattern is also available using the association proxy, which might be a little bit heavy-handed here, however is less code up front, association proxy is at https://docs.sqlalchemy.org/en/13/orm/extensions/associationproxy.html#:

from sqlalchemy.ext.associationproxy import association_proxy

class Task(Base):

    # ...

    description = association_proxy("_description", "description")

    # etc ...

 

   _description = relationship("TaskDefinition", lazy="joined")

hope this helps!

-Mike

--

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/328f63b3-dda4-4b59-a85b-52846e319d53%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Michael P. McDonnell]

Hey Mike - 

First off - thanks for the association proxy idea - I like that, and even if heavy handed -  specifically like the idea that its a *view* in to column as opposed to a manipulable column. 

The next question is - how do I iterate over those association proxy fields within the object? 

So if I'm to do a marshal to json - I'd like to say "my task has a name, description, instructions, etc.."

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/9429f760-5845-47cf-92b6-dee34e9f0c1c%40www.fastmail.com.

[Michael P. McDonnell]

And for clarity's sake - I mean without knowing specifically ahead of time its a "task" object.

[Mike Bayer]

On Fri, Jul 12, 2019, at 6:49 PM, Michael P. McDonnell wrote:

Hey Mike - 

First off - thanks for the association proxy idea - I like that, and even if heavy handed -  specifically like the idea that its a *view* in to column as opposed to a manipulable column. 

The next question is - how do I iterate over those association proxy fields within the object? 

So if I'm to do a marshal to json - I'd like to say "my task has a name, description, instructions, etc.."

the association proxy will show up in the mapping under all_orm_descriptors, e.g.

from sqlalchemy import inspect

d = {

  k getattr(some_object, k) for k in inspect(some_object).mapper.all_orm_descriptors

}

https://docs.sqlalchemy.org/en/13/orm/mapping_api.html?highlight=all_orm_descriptors#sqlalchemy.orm.mapper.Mapper.all_orm_descriptors

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAHmCLHqFB-1spAzQd_VgCLChyaE%2BH5Pb94ymqXtaza6%3DyAbjVQ%40mail.gmail.com.

[Michael P. McDonnell]

Awesome! Thanks! I've been trying to figure out how to do a reliable object -> json conversion (including nested relationships). I'm hoping this is it.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/62388520-54b1-4f93-98e8-90050dbee9ea%40www.fastmail.com.