python - SQLAlchemy filter for files in a folder-file type hierarchy -

i have seen pure sql queries solutions this, sql isn't on point sqlalchemy solution appreciated. i'm using sqlite.

i have hierarchy type structure in sqlalchemy similar normal filesystem - nested nodes leaves @ end. have solution finding files using python, quite slow i'm hoping there's faster way.

the folders defined such:

class folder(base):     __tablename__ = 'folder'     id = column(integer, primary_key=true)     parentid = column(integer, foreignkey('folder.id', ondelete='cascade'),index=true)     type = column(text(50))      children = relationship('folder',                             cascade='all',                             backref=backref('parent', remote_side='folder.id'),                         )      __mapper_args__ = {         'polymorphic_identity': 'folder',         'polymorphic_on': type     }      def getfiles(self):         """ returns list of files in folder         """         flist = []         child in self.children:             flist += child.getfiles()         return flist 

and files:

class file(folder):     __tablename__ = 'file'     id = column(integer, foreignkey('folder.id', ondelete='cascade'), primary_key=true)     name = column(text(50))      __mapper_args__ = {         'polymorphic_identity': 'file',         'inherit_condition': (id == folder.id),     }      def getfiles(self):         """ return self file         """         return [self] 

currently find files filename so:

files = some_folder.getfiles() return [f f in files if file_name in f.name.lower()] 

is there better way query files in folder?

i wasn't able build query how wanted, playing around found solution faster. instead of finding files , searching, traverse each folder , query files in it. resulted in quite performance boost.

def search_files(folderid, search):     files = session.query(file).filter(                                     file.parentid==folderid,                                     file.name.ilike('%{}%'.format(search))).all()     folders = session.query(folder.id).filter_by(parentid=folderid).all()     folder in folders:         files+=search_files(folder.id, search)      return files