def fetch_subtree(self, user_id, query_item_id): # http://docs.peewee-orm.com/en/latest/peewee/api.html#SelectQuery # http://docs.peewee-orm.com/en/latest/peewee/querying.html#common-table-expressions root_query = None if not query_item_id: root_query = (Category.select( Category.id, Category.order, Value(0).alias("level")).join( components.BaseUser, on=(Category.owner == components.BaseUser.id)).where( Category.parent.is_null(), Category.owner.id == user_id).cte(name="roots", recursive=True)) else: root_query = (Category.select( Category.id, Category.order, Value(0).alias("level")).join( components.BaseUser, on=(Category.owner == components.BaseUser.id)).where( Category.parent.id == query_item_id, Category.owner.id == user_id).cte(name="roots", recursive=True)) RTerm = Category.alias() recursive_query = (RTerm.select( RTerm.id, RTerm.order, (root_query.c.level + 1).alias("level")).join( root_query, on=(RTerm.parent == root_query.c.id))) cte = root_query.union_all(recursive_query) tree_query = cte.select_from(cte.c.id, cte.c.order, cte.c.level) items = [item for item in tree_query] # logging.debug(" ".join(["id: %d level:%d order:%d \n" % (item.id, item.level, item.order) for item in items])) return Category.select().where( Category.id << [item.id for item in items]).order_by(Category.path)
def _fetch_path(self, item_id): if not item_id: return "" root_query = (Category.select( Category.id, Category.parent_id, Category.order, Value(0).alias("level")).where(Category.id == item_id).cte( name="child", recursive=True)) RTerm = Category.alias() recursive_query = (RTerm.select( RTerm.id, RTerm.parent_id, RTerm.order, (root_query.c.level + 1).alias("level")).join( root_query, on=(RTerm.id == root_query.c.parent_id))) cte = root_query.union_all(recursive_query) tree_query = cte.select_from(cte.c.id, cte.c.parent_id, cte.c.order, cte.c.level) items = [item.order for item in tree_query] items.reverse() path = ".".join("{:0>4}".format(utils.str_base(item, 32)) for item in items) logging.debug("Path: id=%d %s" % (item_id, path)) return path
def delete_item(self, item_id): from app.notes.model import Note from app.tasks.model import Task # from app.milestones.model import Milestone # from app.worklog.model import Worklog # --- delete -> merge all the stuff to its parents category = None try: category = self.read_item(item_id) # if category.parent: # parent = self.read_item(category.parent) except Category.DoesNotExist: raise components.ResourceNotFoundError() parent = category.parent # merge # We need to have an event dispatch-thingy to notify all the depending modules to move around user_id = components.current_user_id() with components.DB.atomic(): for clazz in [Note, Task # , Milestone ]: # Why can't you just simply update? # clazz.update( # category=parent, # parent.id if parent else None, # edited=datetime.now() # ).where( # clazz.category == category, # under normal circumstances, it can't be None # ).execute() for obj in clazz.select(clazz).join( components.BaseUser, on=(clazz.owner == components.BaseUser.id) ).join(Category, on=(clazz.category == Category.id)).where( clazz.category.id == category. id, # under normal circumstances, it can't be None clazz.owner.id == user_id): obj.category = parent obj.changed() obj.save() # :/ # Why can't you simply update? # Category.update( # parent_id=parent.id, # edited=datetime.now() # ).where( # Category.parent == category, # ).execute() Parent = Category.alias() for child in Category.select(Category).join( components.BaseUser, on=(Category.owner == components.BaseUser.id)).join( Parent, on=(Category.parent == Parent.id)).where( Parent.id == category. id, # under normal circumstances, it can't be None Category.owner.id == user_id): child.parent = parent child.changed() child.save() category.is_deleted = True category.changed() category.save() # TODO: recalculate path for parent # self._flatten_tree_order(user_id) return category