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
def apply_order(query: BaseQuery, sort_keys: str) -> BaseQuery:
if sort_keys:
for key in sort_keys.split(","):
desc = False
if key.startswith("-"):
key = key[1:]
desc = True
column_attr = getattr(GithubUserInfo, key, None)
if column_attr is not None:
query = (query.order_by(column_attr.desc())
if desc else query.order_by(column_attr))
return query
def apply_order(model: DefaultMeta, query: BaseQuery) -> BaseQuery:
sort_keys = request.args.get('sort')
if sort_keys:
for key in sort_keys.split(','):
desc = False
if key.startswith('-'):
key = key[1:]
desc = True
column_attr = getattr(model, key, None)
if column_attr is not None:
query = query.order_by(column_attr.desc()) if desc else query.order_by(column_attr)
return query
def apply_order(model: DefaultMeta, query: BaseQuery) -> BaseQuery:
"""
Functionality of sorting resources, returns sort arguments to query
(example: sort=-id,last_name).
"""
sort_keys = request.args.get("sort")
if sort_keys:
for key in sort_keys.split(","):
desc = False
if key.startswith("-"):
key = key[1:]
desc = True
column_attr = getattr(model, key, None)
if column_attr is not None:
query = (query.order_by(column_attr.desc())
if desc else query.order_by(column_attr))
return query
def apply_engine_specific_hack(self, query: BaseQuery, page, page_size,
order_column) -> BaseQuery:
# MSSQL exception page/limit must have an order by
if (page and page_size and not order_column
and self.session.bind.dialect.name == "mssql"):
pk_name = self.get_pk_name()
return query.order_by(pk_name)
return query
def apply_order_by(self, query: BaseQuery, order_column: str,
order_direction: str) -> BaseQuery:
if order_column != "":
# if Model has custom decorator **renders('<COL_NAME>')**
# this decorator will add a property to the method named *_col_name*
if hasattr(self.obj, order_column):
if hasattr(getattr(self.obj, order_column), "_col_name"):
order_column = getattr(self._get_attr(order_column),
"_col_name")
_order_column = self._get_attr(order_column) or order_column
if is_column_dotted(order_column):
query = self._query_join_relation(
query, get_column_root_relation(order_column))
if order_direction == "asc":
query = query.order_by(asc(_order_column))
else:
query = query.order_by(desc(_order_column))
return query
def ql_don_hang_all(page=1):
if not current_user.is_authenticated or current_user.ma_loai_nguoi_dung != 1:
return redirect(url_for('dang_nhap', next=request.url))
query = BaseQuery(Hoa_don, dbSession)
page_filter = query.order_by(desc(Hoa_don.ngay_tao_hoa_don)).paginate(
page, 10, False)
tieu_de = 'Tất cả đơn hàng'
return render_template('Quan_ly/QL_don_hang/QL_don_hang_all.html',
page_filter=page_filter,
tieu_de=tieu_de)
def set_sort_order(query: BaseQuery,
obj_model: Model,
sort_order="asc",
sort_by="id",
*args,
**kwargs) -> BaseQuery:
if sort_order is not None:
sort = asc if sort_order == "asc" else desc
else:
sort = asc
# sort = asc if sort_order == "asc" else desc
column = next((c for c in inspect(obj_model).columns if c.name == sort_by),
"id")
return query.order_by(sort(column))
def force_pagination(query: BaseQuery, order=None):
query = query.order_by(order if order is not None else desc('id'))
page = int(request.values.get('page', 1))
per_page = int(request.values.get('per_page', FETCH_LIMIT))
pagination = query.paginate(page, per_page,
error_out=False) # type: Pagination
return jsonify({
'rows': [instance.to_json() for instance in pagination.items],
'max_page':
pagination.pages,
'curr_page':
pagination.page,
'next_page':
pagination.next_num,
'prev_page':
pagination.prev_num,
})
def _construct_query(
query: BaseQuery,
filter: BinaryExpression = None,
order: BinaryExpression = None,
) -> BaseQuery:
"""
A convenience function to save code space for query generations. Takes filters
and order_bys and applies them to the query, returning a query ready to be ran.
:param query: A query that can be built upon
:param filter: A SQLAlchemy query filter expression
:param order: A SQLAlchemy query order_by expression
:return: A Flask-SQLAlchemy ``BaseQuery``
"""
if filter is not None:
query = query.filter(filter)
if order is not None:
query = query.order_by(order)
return query
def get_geonames_sorted(geonames: BaseQuery) -> BaseQuery:
return geonames.order_by(GeoName.population.desc())
