Column_property / hybrid_property for union a list

Question

The situation is as follows. I have a table with two lists includes relationships to another table. A Child entry can have one or two parents (mostly two ;). A parent distinguishes his children per family. This structure is fixed.

Now I want have a column named all_children which includes all children of all families directly in the same table (because of direct graphene integration). There are two main approaches: column_property or hybrid_property. But unfortunately all tests with both approaches for example the naive idea to simply concatenate the two lists with python failed

class Parent(Base):

    id = Column(Integer, primary_key=True, autoincrement="auto")
    children_family_1 = relationship(Child, backref='child_1', cascade='all, delete', lazy='select', foreign_keys='[Child.ref_1_id]')
    children_family_2 relationship(Child, backref='child_2', cascade='all, delete', lazy='select', foreign_keys='[Child.ref_2_id]')
    all_children = column_property(children_family_1 + children_family_2)

    @hybrid_property
    def all_children(self):
         return self.children_family_1 + self.children_family_2

    @all_children.expression
    def all_children(cls):
         return select(??)


class Child(Base):

    id = Column(Integer, primary_key=True, autoincrement="auto")
    ref_1_id = Column(Integer, ForeignKey('parent.id'))
    ref_2_id = Column(Integer, ForeignKey('parent.id'))

It seems, that I haven't actually enough knowledge in sqlalchemy to evaluate and implement the right approach. Please tell me, which I should use for this use case and how can I implement it as described. Thanks in advance.

score 0 · Answer 1 · edited Feb 01 '22 at 09:12

@hybrid_property
def children(self) -> [Child]:
    if self.children_family_1 is None:
        self.children_family_1 = []

    if self.children_family_2 is None:
        self.children_family_2 = []

    return self.children_family_1 + self.children_family_2

    @children.expression
    def children(cls) -> [Child]:
        return (
            select().where(
                or_(
                    Child.ref_1_id == cls.id,
                    Child.ref_2_id == cls.id
                )
            )
        )

Thanks for this explanations about the differences -> https://martinheinz.dev/blog/28

Column_property / hybrid_property for union a list

1 Answers1