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.."
On Fri, Jul 12, 2019 at 11:19 AM Mike Bayer <[email protected]> wrote: > > > On Fri, Jul 12, 2019, at 11:20 AM, Michael P. McDonnell wrote: > > 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? > > > 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. > <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 [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/328f63b3-dda4-4b59-a85b-52846e319d53%40googlegroups.com > <https://groups.google.com/d/msgid/sqlalchemy/328f63b3-dda4-4b59-a85b-52846e319d53%40googlegroups.com?utm_medium=email&utm_source=footer> > . > 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/9429f760-5845-47cf-92b6-dee34e9f0c1c%40www.fastmail.com > <https://groups.google.com/d/msgid/sqlalchemy/9429f760-5845-47cf-92b6-dee34e9f0c1c%40www.fastmail.com?utm_medium=email&utm_source=footer> > . > 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/CAHmCLHqFB-1spAzQd_VgCLChyaE%2BH5Pb94ymqXtaza6%3DyAbjVQ%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
