And for clarity's sake - I mean without knowing specifically ahead of time its a "task" object.
On Fri, Jul 12, 2019 at 5:49 PM Michael P. McDonnell <[email protected]> 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.." > > 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/CAHmCLHpLTEgshctSDJaXCXiHpHrjAvD553LUJO8NQtHFQQQSnQ%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
