On Wed, Sep 9, 2020, at 2:36 PM, Bobby Rullo wrote:
> Hi there,
>
> I'm trying to create a relationship for a Mxin that is agnostic to what the
> primary key of the mixed object is.
>
> Basically we have this:
>
> class TransitionBase(SurrogatePK, Model):
> __abstract__ = True
>
> obj_id = Column(String, nullable=False, index=True)
> state = Column(String, nullable=False, index=True)
> ...(more stuff)...
>
> class HasStateMixin:
> @staticmethod
> def get_state_class() -> Type[TransitionBase]:
> raise NotImplementedError()
>
> @declared_attr
> def transitions(cls) -> List[TransitionBase]:
> state_cls = cls.get_state_class()
> return relationship(
> state_cls,
> primaryjoin=foreign(state_cls.obj_id) == remote(cls.id), # type:
> ignore
> order_by=state_cls.id.desc(),
> )
>
> And it works well, as long as the `id` property is a normal Column. Eg:
>
> class Transition(TransitionBase):
> __tablename__ = ''transitions'
>
>
> class Obj(Model, HasStateMachineMixin):
> __tablename__ = 'obj'
> __table_args__ = {'schema': test_schema}
>
> id = Column(String, primary_key=True)
>
> @staticmethod
> def get_state_class() -> Type[TransitionBase]:
> return Transition
>
> But now I have a case where the primary key of the mixed class is three
> columns. My first instinct was to use a hybrid property like this:
>
> class ThreePrimaryKeys(HasStateMachineMixin):
> a = Column(String, primary_key=True)
> b = Column(String, primary_key=True)
> c = Column(String, primary_key=True)
>
> @hybrid_property
> def id(self):
> return f'/{self.a}/{self.b}/{self.c}'
>
> @id.expression
> def id(cls):
> return func.concat(
> '/', cls.a, '/', cls.b, '/', cls.c,
> )
>
> But that doesn't work: when I create a ThreePrimaryKeys() and then call
> obj.transitions.append(MyTransition(state='foo')) it doesn't properly persist
> the full concatenated ID - weirdly, it just uses column 'c' (or the
> equivalent - type names have been changed to protect the innocent)
>
> My question is: should this work? And if so, what am I doing wrong? If not,
> do you have an alternate approach?
I'm not really sure why it doesn't work but the correct approach here is to
have a composite ForeignKeyConstraint on the class that refers to
ThreePrimaryKeys, or at least a join condition that links the three columns
separately to three columns on the referencing object (but there should really
be a FK constraint, as it looks like you are trying to do reasonable relational
schema design). you can get information on what kind of primary key a mapped
class has by using inspect(class).primary_key . your TransitionBase thing
would need to dynamically have multiple "obj_id" columns added based on what
kind of target it is hitting.
There's a lot of ways to do this, including in your transitions() method, you'd
need to look at the class you're linking to, get the list of primary key
columns, then add that many FK columns to the immediate class, set up a
ForeignKeyConstraint for them and add that to the Table also (like
cls.__table__.append_constraint()) , then the relationship() would just work.
or you could build up the primary join using and_(c1 == fk1, c2 == fk2, ..)
a bit of a handwavy answer but that's the general idea. making a
concatenated string like that is not good relational design, it's denormalized
and can't be properly indexed.
>
> Thanks in advance,
>
> Bobby
>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/0083c5d4-cd66-4346-8108-f85a8909d69an%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/0083c5d4-cd66-4346-8108-f85a8909d69an%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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/491ffccb-bd66-4dc2-a29f-2a73943f8c22%40www.fastmail.com.
