this is called a "Generic foreign key" and it's not really a real relational database pattern. There are a series of examples in https://docs.sqlalchemy.org/en/13/orm/examples.html#module-examples.generic_associations that show four different ways to achieve this pattern, one of which is the "generic foreign key" that is for example what Django offers, where the combination of a "discriminator" (here you call it table_name) and an id can link to different source tables. However there are three other ways given of doing the same thing that all use correct referential integrity. Which one to use depends on how you need to be querying the "entity_type" table, however all four will store essentially the same information, just in different formats.
On Sat, Mar 14, 2020, at 9:55 AM, Mark Aquino wrote: > Is it possible to create a relationship via the table name as the "foreign > key"? I tried playing around with the foreign and remote options and tried > utilizing what's described here: > https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#non-relational-comparisons-materialized-path > but I couldn't get it to work for my case. > > I have a table that has entity types and a table_name column, e.g. > > class EntityType(Base): > __tablename__ = "entity_type" > id = Column(UUID, primary_key=True, server_default=FetchedValue()) > table_name = Column(String, nullable=False) > prefix = Column(Text, unique=True, nullable=False) > alt_prefix = Column(Text) > ui_label = Column(Text, unique=True, nullable=False) > entry_key = Column(Text, unique=True, nullable=False) > > config_entity_column_ui_visibility = > relationship("ConfigEntityColumnUiVisibility") > > def __repr__(self): > return ( > f"<EntityType(id={self.id} table_name={self.table_name} > prefix={self.prefix} ui_label={self.ui_label} " > f"entry_key={self.entry_key}>" > ) > > I want to create a relationship to this table from other tables via their > table name rather than a column on the table. Is this possible? > > e.g. > class GenericEntityTypeMixin: > > @declared_attr.cascading > def prefix(cls) -> ColumnProperty: > from webapp.database.orm.models import EntityType > > return column_property( > select([EntityType.prefix]) > .where(EntityType.table_name == cls.__tablename__) > .as_scalar(), > info={"key": "prefix"}, > ) > > @hybrid_property > def qualified_id(self): > return f"{self.prefix}-{self.visible_id}" > > *# @declared_attr ** # def entity_type(cls) -> RelationshipProperty: ** # how do we create relationship without using a column object on the foreign side? ** ** *@declared_attr > def entity_type_entry_key(cls): > return association_proxy("entity_type", "entry_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+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/d54d0af1-7e8e-44f1-8398-989b09b3ea8e%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/d54d0af1-7e8e-44f1-8398-989b09b3ea8e%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/af0e6d27-0944-4027-b2b0-ba702bc08c1c%40www.fastmail.com.
