python - query to hierarchical mapper -

python - query to hierarchical mapper -

i have 2 models, related many-to-many, 1 of them hierarchical model:

#hierarchical model class tag(base): __tablename__ = "tags" id = column(integer, primary_key=true) name = column(string) tag.parent_id = column(integer, foreignkey(tag.id, ondelete='cascade')) tag.childs = relationship(tag, backref=backref('parent', remote_side=[tag.id]), cascade="all, delete") class subject(base): __tablename__ = "subjects" id = column(integer, primary_key=true, doc="id") name = column(string) tags = relationship(tag, secondary="tags_subjects", backref="subjects") #many-to-many relations model class tagssubjects(base): __tablename__ = "tags_subjects" id = column(integer, primary_key=true) tag_id = column(integer, foreignkey("tags.id")) subject_id = column(integer, foreignkey("subjects.id"))

so, i'll seek explain want do... want create 1 (or several) query, search subject's objects, have 'name' field value 'foo' or has related tags having names values 'foo' or has related tags, has 1 or more parents (or above hierarchy) tag 'name' value 'foo'

i've tried somethis this:

>>> subjects = session.query(subject).filter(or_( subject.name.ilike('%{0}%'.format('foo')), subject.tags.any( tag.name.ilike('%{0}%'.format('foo'))) )).order_by(subject.name).all()

but isn't right , "flat" query, without hierarchical feature :( how sqlalchemy's api? thanks!

p.s. i'm using sqlite backend

python sqlalchemy