python - SQLAlchemy chaining association proxy for great grandchildren? -

python - SQLAlchemy chaining association proxy for great grandchildren? -

i have 4 classes so: group, parent, child, toy.

group has parents relationship pointing parent parent has children relationship pointing child child has toys relationship pointing toy

parent has toys association_proxy produces toys parent's children have.

i want able toys in group. tried create association_proxy on group links parent's toys, produces this:

[[<toy 1>, <toy 2>], [], [], []]

when want this:

[<toy 1>, <toy 2>]

if parent's children don't have toys, toys association proxy []. however, sec association proxy doesn't know exclude empty lists. also, lists should collapsed. there anyway work?

class group(db.model): id = db.column(db.integer, primary_key=true) created_at = db.column(db.datetime, default=utils.get_now_datetime) name = db.column(db.string(80, convert_unicode=true)) # relationships parents = db.relationship('parent', backref='group') toys = association_proxy('parents', 'toys') class parent(db.model): id = db.column(db.integer, primary_key=true) group_id = db.column(db.integer, db.foreignkey('group.id', ondelete='cascade')) created_at = db.column(db.datetime, default=utils.get_now_datetime) first_name = db.column(db.string(80, convert_unicode=true)) last_name = db.column(db.string(80, convert_unicode=true)) children = db.relationship('child', backref='parent', cascade='all, delete') toys = association_proxy('children', 'toys') class child(db.model): id = db.column(db.integer, primary_key=true) parent_id = db.column(db.integer, db.foreignkey('parent.id', ondelete='cascade')) created_at = db.column(db.datetime, default=utils.get_now_datetime) class toy(db.model): id = db.column(db.integer, primary_key=true) child_id = db.column(db.integer, db.foreignkey('child.id', ondelete='cascade')) created_at = db.column(db.datetime, default=utils.get_now_datetime) kid = db.relationship('child', backref=db.backref("toys",cascade="all, delete-orphan", order_by="desc(toy.id)"))

given retrieval , view (as mentioned in comment, adding ambiguous), rather viewonly relationship without association_proxy:

class group(db.model): # ... toys = relationship('toy', secondary="join(group, parent, group.id == parent.group_id).join(child, parent.id == child.parent_id)", primaryjoin="and_(group.id == parent.group_id, parent.id == child.parent_id)", secondaryjoin="child.id == toy.child_id", viewonly=true, )

note new feature of sqlalchemy , describe in composite “secondary” joins section of documentation.

then can utilize query:

group_id = 123 grouping = session.query(group).get(group_id) print(group.toys)

or can utilize filter, find grouping contains toy name "super mario" can do:

group = session.query(group).filter(group.toys.any(toy.name == "super mario"))

but in reality can simple query, or create query-enabled property. see customizing column properties section of documentation, can utilize of simple property, column_property or hybrid attribute.

python sqlalchemy flask-sqlalchemy