def apply_pagination(self, query: BaseQuery, page: Optional[int],
page_size: Optional[int]) -> BaseQuery:
if page and page_size:
query = query.offset(page * page_size)
if page_size:
query = query.limit(page_size)
return query
def model_filter_by_get_params(model: DefaultMeta, query: BaseQuery,
args: dict) -> BaseQuery:
"""
Here is implemented a search and filtering the model by request url params. There are several possible variants of
search, and, of course, they can be easily combined to attain the necessary result.
Method received three obligatory parameters: model - sqlalchemy orm model class, query - :class:`BaseQuery` object
to filtering and args - dict of already parsed url params.
1. User can look for the model instances by putting columns names directly and their theoretical values, like:
http://localhost:5000/api/users?username=dima&name=dmytro. Such queries will select only exact matches.
2. To search without an exact match use `-like` ending after db columns names. It works only if column has type
:class:`sqlalchemy.sql.sqltypes.String` and gives the result from sql `LIKE` statement. Common url looks
like: http://localhost:5000/api/users?username-like=ma
3. To sort an output use 'ordered-by' and 'ordered-by-desc' parameter and specify db column name to order by:
http://localhost:5000/api/users?name-like=a&ordered-by=username
4. To restrict the number of results use 'limit' and 'offset' statements together or separately:
http://localhost:5000/api/users?name-like=a&ordered-by=username&limit=2&offset=3
A simple use case is below::
from flask import request
from app import db
@route('/')
def index():
users = User.query
users = model_filter_by_get_params(User, users, request.args).all()
If given params are not valid, they will be ignored.
The function can pe applied for different models and queries and can be expanded if it is necessary.
:param model: model base class
:type model: DefaultMeta
:param query: not executed query object to filtering
:type query: BaseQuery
:param args: url query parameters
:type args: dict
:return: not executed filtered query object
:rtype: BaseQuery
"""
model_columns = model.__table__.c.keys()
for key, value in args.items():
if key == 'ordered-by':
if value in model_columns:
query = query.order_by(getattr(model, value))
elif key == 'ordered-by-desc':
if value in model_columns:
query = query.order_by(desc(getattr(model, value)))
elif key.endswith('-like'):
attr = key.split('-like')[0]
if attr in model_columns and isinstance(
model.__table__.c.get(attr).type, String):
query = query.filter(getattr(model, attr).ilike(f'%{value}%'))
elif key in model_columns:
query = query.filter(getattr(model, key) == value)
if 'limit' in args:
query = query.limit(int(args.get('limit')))
if 'offset' in args:
query = query.offset(int(args.get('offset')))
return query
