I guess I could do that: I am using a joined table inheritance schema for most of the classes in my data model (and that base table does have an entity_type_id) but was trying to link a few "entity_type"s to some of the classes that didn't inherit from the base.
The difference in the Django model is that the joined class (Addresses) has a parent_id column on it, and this line is key: "*class_.id* == foreign( remote(Address.parent_id))". without some column on the parent class to map to in the primaryjoin clause the relationship function doesn't seem to work. I don't think the discriminator there is necessary to create relationship because there IS a "pseudo foreign key", it just has no referential integrity. A bit of a digression but I can't really tell what the where clause part is necessary for tbh (Address.discriminator == discriminator). When would class_.id == Address.parent_ id but the class name != Address.discriminator? It would be nice if you could somehow override it and directly specify how to create the relationship with a custom query, but oh well... On Monday, March 16, 2020 at 1:50:27 PM UTC-4, Mike Bayer wrote: > > > > On Mon, Mar 16, 2020, at 1:23 PM, Mark Aquino wrote: > > Unfortunately none of those recipes work for what I'm trying to > accomplish, and the mapper just complains that there is no "unambiguous" > foreign key column to map the two classes. > > Normal referential integrity rules would dictate that I create a column on > the related class that referred to the entity_type.id, but in a > non-polymorphic/shared table setting it seems completely unnecessary.: what > is the point in having a column on a single table like called > "entity_type_id" that has the same value in every row? > > I need the relationship to be constructed by a query that looks like: > > select entity_type.*, something.* > from entity_type, something > where entity_type.table_name = 'something'; > > > would you perhaps want to use joined table inheritance? that's the kind > of query it emits. however the structure you gave does seem to be > equivalent to Django's generic foreign key to me. the "table_name" is > known as the "discriminator". > > > > or > select something.*, (select * from entity_type where > entity_type.table_name='something') from something; > > Is it impossible to create a relationship like this using sqlalchemy? > > sqlalchemy doesn't allow this: > > @event.listens_for(GenericEntityTypeMixin, "mapper_configured", > propagate=True) > def setup_listener(mapper, class_): > discriminator = class_.__tablename__ > class_.comments = relationship( > "EntityType", > primaryjoin= > foreign(remote(EntityType.table_name)) == discriminator > , > viewonly=True > ) > > On Saturday, March 14, 2020 at 12:55:03 PM UTC-4, Mike Bayer wrote: > > 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 sqlal...@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 sqlal...@googlegroups.com <javascript:>. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/49e3f953-68b2-4810-8002-7035386b8e39%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/49e3f953-68b2-4810-8002-7035386b8e39%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/fe8a2ca9-9ba7-45f5-b4f9-84d6ea43cea2%40googlegroups.com.
