def page(where, sort, pageable, model, name) -> DataPage: count = count_table(name) table = get_table_model(name) stmt = select(table).where(build_where_expression(where)).order_by(sort) offset = pageable.pageSize * (pageable.pageNumber - 1) stmt = stmt.offset(offset).limit(pageable.pageSize) result = [] with engine.connect() as conn: res = conn.execute(stmt) conn.commit() for row in res: for item in row: result.append(parse_obj(model, item)) return build_data_pages(pageable, result, count)
def query_with_pagination(collection_name, pagination, base_model, query_dict=None, sort_dict=None): count = count_table(collection_name) table = get_table_model(collection_name) result = [] session = Session(engine, future=True) stmt = select(table) for key in query_dict.keys(): if isinstance(query_dict.get(key), regex.Regex): value = query_dict.get(key) pattern = getattr(value, 'pattern') if len(pattern) > 0: stmt = stmt.where(eq(getattr(table, key), pattern)) else: stmt = stmt.where(eq(getattr(table, key), pattern)) if isinstance(sort_dict[0], str): order_field = sort_dict[0] if sort_dict[1] == pymongo.DESCENDING: order_seq = "desc" else: order_seq = "asc" if order_seq == "desc": stmt = stmt.order_by(getattr(table, order_field).desc()) else: for tup in sort_dict: order_field = tup[0] if tup[1] == pymongo.DESCENDING: order_seq = "desc" if tup[1] == pymongo.ASCENDING: order_seq = "asc" if order_seq == "desc": stmt = stmt.order_by(order_field.desc()) offset = pagination.pageSize * (pagination.pageNumber - 1) stmt = stmt.offset(offset).limit(pagination.pageSize) res = session.execute(stmt) for row in res: for item in row: result.append(parse_obj(base_model, item)) return build_data_pages(pagination, result, count)