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. 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+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@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 > > <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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@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/9429f760-5845-47cf-92b6-dee34e9f0c1c%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.
