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)