relationship without using a column value (using table name)?

[Mark Aquino]

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")

[Mike Bayer]

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.

--

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/d54d0af1-7e8e-44f1-8398-989b09b3ea8e%40googlegroups.com.

[Mark Aquino]

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';

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
    )

To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.

[Mike Bayer]

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".  

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/49e3f953-68b2-4810-8002-7035386b8e39%40googlegroups.com.

[Mark Aquino]

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...

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/49e3f953-68b2-4810-8002-7035386b8e39%40googlegroups.com.