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
)
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 <javascript:>.
> 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/49e3f953-68b2-4810-8002-7035386b8e39%40googlegroups.com.
