def query(self, entity, limit=None, offset=None, limit_fields=None,
order_by=None, desc=False, field_names=[], **kw):
query = self.session.query(entity)
count = query.count()
if order_by is not None:
if self.is_relation(entity, order_by):
mapper = class_mapper(entity)
class_ = None
for prop in mapper.iterate_properties:
try:
class_ = prop.mapper.class_
except (AttributeError, KeyError):
pass
query = self.session.query(entity).join(order_by)
f = self.get_view_field_name(class_, field_names)
field = self.get_field(class_, f)
else:
field = self.get_field(entity, order_by)
if desc:
field = _desc(field)
query = query.order_by(field)
if offset is not None:
query = query.offset(offset)
if limit is not None:
query = query.limit(limit)
objs = query.all()
return count, objs
def gen_datatable(request,
db_session,
Model,
filter_=None,
search_columns=None,
ret_columns=None,
order_by=None,
desc=True):
draw = int(request.args['draw'][0])
start = int(request.args['start'][0])
length = int(request.args['length'][0])
search_value = request.args.get('search[value]')
query = db_session.query(Model)
columns = [column.name for column in Model.__table__.columns._all_columns]
if filter_:
query = filter_(query)
records_total = query.count()
if records_total == 0:
return response.json({
'data': [],
'draw': draw,
'recordsTotal': records_total,
'recordsFiltered': records_total
})
if search_value:
if search_columns:
search_condition = [
getattr(Model, attribute).like("%{}%".format(search_value))
for attribute in search_columns
]
else:
search_condition = [
getattr(Model, attribute).like("%{}%".format(search_value))
for attribute in columns
]
records_total = query.filter(or_(*search_condition)).count()
query = query.filter(or_(*search_condition))
if order_by:
query = query.order_by(_desc(getattr(
Model, order_by))) if desc else query.order_by(
getattr(Model, order_by))
records_filtered = records_total
results = query.offset(start).limit(length).all()
if ret_columns:
ret = [[getattr(result, attribute) for attribute in ret_columns]
for result in results]
else:
ret = [[getattr(result, attribute) for attribute in columns]
for result in results]
return response.json({
'data': ret,
'draw': draw,
'recordsTotal': records_total,
'recordsFiltered': records_filtered
})
def get_all(cls, order_by='id', start=0, limit=10, desc=False):
'''
:param order_by: One of ``'id'``, ``'live_count'`` or
``'updated_time'``
'''
query = session.query(User)
order_by = getattr(User, order_by)
if desc:
order_by = _desc(order_by)
users = query.order_by(order_by).offset(start).limit(limit).all()
return [user.to_dict() for user in users]
def query(self, entity, limit=None, offset=None, limit_fields=None,
order_by=None, desc=False, field_names=[], filters={},
substring_filters=[], **kw):
entity = resolve_entity(entity)
query = self.session.query(entity)
filters = self._modify_params_for_dates(entity, filters)
filters = self._modify_params_for_relationships(entity, filters)
for field_name, value in filters.items():
field = getattr(entity, field_name)
if self.is_relation(entity, field_name) and isinstance(value, list):
value = value[0]
query = query.filter(field.contains(value))
elif field_name in substring_filters and self.is_string(entity, field_name):
escaped_value = re.sub('[\\\\%\\[\\]_]', '\\\\\g<0>', value.lower())
query = query.filter(func.lower(field).contains(escaped_value, escape='\\'))
else:
query = query.filter(field==value)
count = query.count()
if order_by is not None:
if self.is_relation(entity, order_by):
mapper = class_mapper(entity)
class_ = None
for prop in mapper.iterate_properties:
try:
class_ = prop.mapper.class_
except (AttributeError, KeyError):
pass
query = self.session.query(entity).join(order_by)
f = self.get_view_field_name(class_, field_names)
field = self.get_field(class_, f)
else:
field = self.get_field(entity, order_by)
if desc:
field = _desc(field)
query = query.order_by(field)
if offset is not None:
query = query.offset(offset)
if limit is not None:
query = query.limit(limit)
objs = query.all()
return count, objs
def query(self, entity, limit=None, offset=None, limit_fields=None, order_by=None, desc=False, **kw):
query = self.session.query(entity)
count = query.count()
if order_by is not None:
field = self.get_field(entity, order_by)
if desc:
field = _desc(field)
query = query.order_by(field)
if offset is not None:
query = query.offset(offset)
if limit is not None:
query = query.limit(limit)
objs = query.all()
return count, objs
def query(self,
entity,
limit=None,
offset=None,
limit_fields=None,
order_by=None,
desc=False,
**kw):
query = self.session.query(entity)
count = query.count()
if order_by is not None:
field = self.get_field(entity, order_by)
if desc:
field = _desc(field)
query = query.order_by(field)
if offset is not None:
query = query.offset(offset)
if limit is not None:
query = query.limit(limit)
objs = query.all()
return count, objs
def query(self, entity, limit=None, offset=None, limit_fields=None,
order_by=None, desc=False, field_names=[], filters={},
substring_filters=[], **kw):
'''Perform database query with given filters
Based on the original SAORMProvider with query_modifier and
some subtle enhancements (fail-safe modify_params, filter parsing)
'''
query = self.session.query(entity)
if self.query_modifier:
query = self.query_modifier(query)
# Process filters from url
exc = False
try:
filters = self._modify_params_for_dates(entity, filters)
except ValueError as e:
log.info('Could not parse date filters', exc_info=True)
# flash('Could not parse date filters: %s.' % e.message, 'error')
exc = True
try:
filters = self._modify_params_for_relationships(entity, filters)
except (ValueError, AttributeError) as e:
log.info('Could not parse relationship filters', exc_info=True)
# flash('Could not parse relationship filters: %s. '
# 'You can only filter by the IDs of relationships, not by their names.' % e.message, 'error')
exc = True
if exc:
# Since any non-parsed filter is bad, we just have to ignore them all now
filters = {}
for field_name, value in filters.iteritems():
try:
field = getattr(entity, field_name)
if self.is_relation(entity, field_name) and isinstance(value, list): # pragma: no cover
value = value[0]
query = query.filter(field.contains(value))
else:
# query = query.filter(field == value)
typ = self.get_field(entity, field_name).type
if isinstance(typ, Integer):
value = int(value)
query = query.filter(field == value)
elif isinstance(typ, Numeric): # pragma: no cover
value = float(value)
query = query.filter(field == value)
elif field_name in substring_filters and self.is_string(entity, field_name):
# escaped_value = re.sub('[\\\\%\\[\\]_]', '\\\\\g<0>', value.lower())
# query = query.filter(func.lower(field).contains(escaped_value, escape='\\'))
query = query.filter(func.lower(field).contains(value.lower()))
else:
query = query.filter(field == value)
except:
log.warn('Could not create filter on query', exc_info=True)
# Get total count
count = query.count()
# Process ordering
if order_by is not None: # pragma: no cover
if self.is_relation(entity, order_by):
mapper = class_mapper(entity)
class_ = None
for prop in mapper.iterate_properties:
try:
class_ = prop.mapper.class_
except (AttributeError, KeyError):
pass
query = self.session.query(entity).join(order_by)
f = self.get_view_field_name(class_, field_names)
field = self.get_field(class_, f)
else:
field = self.get_field(entity, order_by)
if desc:
field = _desc(field)
query = query.order_by(field)
# Process pager options
if offset is not None: # pragma: no cover
query = query.offset(offset)
if limit is not None: # pragma: no cover
query = query.limit(limit)
return count, query
def query(self, entity, limit=None, offset=None, limit_fields=None,
order_by=None, desc=False, field_names=[], filters={},
substring_filters=[], **kw):
# Based on the original SAORMProvider with query_modifier and
# some subtle enhancements (fail-safe modify_params, filter parsing)
query = self.session.query(entity)
if self.query_modifier:
query = self.query_modifier(query)
# Process filters from url
exc = False
try:
filters = self._modify_params_for_dates(entity, filters)
except ValueError as e:
log.info('Could not parse date filters', exc_info=True)
# flash('Could not parse date filters: %s.' % e.message, 'error')
exc = True
try:
filters = self._modify_params_for_relationships(entity, filters)
except (ValueError, AttributeError) as e:
log.info('Could not parse relationship filters', exc_info=True)
# flash('Could not parse relationship filters: %s. '
# 'You can only filter by the IDs of relationships, not by their names.' % e.message, 'error')
exc = True
if exc:
# Since any non-parsed filter is bad, we just have to ignore them all now
filters = {}
for field_name, value in filters.iteritems():
try:
field = getattr(entity, field_name)
if self.is_relation(entity, field_name) and isinstance(value, list):
value = value[0]
query = query.filter(field.contains(value))
else:
# query = query.filter(field == value)
typ = self.get_field(entity, field_name).type
if isinstance(typ, Integer):
value = int(value)
query = query.filter(field == value)
elif isinstance(typ, Numeric):
value = float(value)
query = query.filter(field == value)
elif field_name in substring_filters and self.is_string(entity, field_name):
escaped_value = re.sub('[\\\\%\\[\\]_]', '\\\\\g<0>', value.lower())
query = query.filter(func.lower(field).contains(escaped_value, escape='\\'))
else:
query = query.filter(field == value)
except:
log.warn('Could not create filter on query', exc_info=True)
# Get total count
count = query.count()
# Process ordering
if order_by is not None:
if self.is_relation(entity, order_by):
mapper = class_mapper(entity)
class_ = None
for prop in mapper.iterate_properties:
try:
class_ = prop.mapper.class_
except (AttributeError, KeyError):
pass
query = self.session.query(entity).join(order_by)
f = self.get_view_field_name(class_, field_names)
field = self.get_field(class_, f)
else:
field = self.get_field(entity, order_by)
if desc:
field = _desc(field)
query = query.order_by(field)
# Process pager options
if offset is not None:
query = query.offset(offset)
if limit is not None:
query = query.limit(limit)
return count, query
def query(self, entity, limit=None, offset=None, limit_fields=None,
order_by=None, desc=False, filters={},
substring_filters=[], search_related=False, related_field_names=None,
**kw):
entity = resolve_entity(entity)
query = self.session.query(entity)
filters = self._modify_params_for_dates(entity, filters)
if search_related:
# Values for related fields contain the text to search
filters = self._modify_params_for_related_searches(entity, filters,
view_names=related_field_names,
substrings=substring_filters)
else:
# Values for related fields contain the primary key
filters = self._modify_params_for_relationships(entity, filters)
for field_name, value in filters.items():
field = getattr(entity, field_name)
if self.is_relation(entity, field_name) and isinstance(value, list):
related_class = self._get_related_class(entity, field_name)
related_pk = self.get_primary_field(related_class)
related_pk_col = getattr(related_class, related_pk)
related_pk_values = (getattr(v, related_pk) for v in value)
if self._relates_many(entity, field_name):
field_filter = field.any(related_pk_col.in_(related_pk_values))
else:
field_filter = field.has(related_pk_col.in_(related_pk_values))
query = query.filter(field_filter)
elif field_name in substring_filters and self.is_string(entity, field_name):
escaped_value = self._escape_like(value.lower())
query = query.filter(func.lower(field).contains(escaped_value, escape='*'))
else:
query = query.filter(field==value)
count = query.count()
if order_by is not None:
if not isinstance(order_by, (tuple, list)):
order_by = [order_by]
if not isinstance(desc, (tuple, list)):
desc = [desc]
for sort_by, sort_descending in zip_longest(order_by, desc):
if self.is_relation(entity, sort_by):
mapper = class_mapper(entity)
class_ = self._get_related_class(entity, sort_by)
query = query.outerjoin(sort_by)
f = self.get_view_field_name(class_, related_field_names)
field = self.get_field(class_, f)
else:
field = self.get_field(entity, sort_by)
if sort_descending:
field = _desc(field)
query = query.order_by(field)
if offset is not None:
query = query.offset(offset)
if limit is not None:
query = query.limit(limit)
objs = query.all()
return count, objs
def query(self,
entity,
limit=None,
offset=None,
limit_fields=None,
order_by=None,
desc=False,
filters={},
substring_filters=[],
search_related=False,
related_field_names=None,
**kw):
entity = resolve_entity(entity)
query = self.session.query(entity)
filters = self._modify_params_for_dates(entity, filters)
if search_related:
# Values for related fields contain the text to search
filters = self._modify_params_for_related_searches(
entity,
filters,
view_names=related_field_names,
substrings=substring_filters)
else:
# Values for related fields contain the primary key
filters = self._modify_params_for_relationships(entity, filters)
for field_name, value in filters.items():
field = getattr(entity, field_name)
if self.is_relation(entity, field_name) and isinstance(
value, list):
related_class = self._get_related_class(entity, field_name)
related_pk = self.get_primary_field(related_class)
related_pk_col = getattr(related_class, related_pk)
related_pk_values = (getattr(v, related_pk) for v in value)
if self._relates_many(entity, field_name):
field_filter = field.any(
related_pk_col.in_(related_pk_values))
else:
field_filter = field.has(
related_pk_col.in_(related_pk_values))
query = query.filter(field_filter)
elif field_name in substring_filters and self.is_string(
entity, field_name):
escaped_value = self._escape_like(value.lower())
query = query.filter(
func.lower(field).contains(escaped_value, escape='*'))
else:
query = query.filter(field == value)
count = query.count()
if order_by is not None:
if not isinstance(order_by, (tuple, list)):
order_by = [order_by]
if not isinstance(desc, (tuple, list)):
desc = [desc]
for sort_by, sort_descending in zip_longest(order_by, desc):
if self.is_relation(entity, sort_by):
mapper = class_mapper(entity)
class_ = self._get_related_class(entity, sort_by)
query = query.outerjoin(sort_by)
f = self.get_view_field_name(class_, related_field_names)
field = self.get_field(class_, f)
else:
field = self.get_field(entity, sort_by)
if sort_descending:
field = _desc(field)
query = query.order_by(field)
if offset is not None:
query = query.offset(offset)
if limit is not None:
query = query.limit(limit)
objs = query.all()
return count, objs
def query(self,
entity,
limit=None,
offset=None,
limit_fields=None,
order_by=None,
desc=False,
field_names=[],
filters={},
substring_filters=[],
**kw):
entity = resolve_entity(entity)
query = self.session.query(entity)
filters = self._modify_params_for_dates(entity, filters)
filters = self._modify_params_for_relationships(entity, filters)
for field_name, value in filters.items():
field = getattr(entity, field_name)
if self.is_relation(entity, field_name) and isinstance(
value, list):
value = value[0]
query = query.filter(field.contains(value))
elif field_name in substring_filters and self.is_string(
entity, field_name):
escaped_value = re.sub('[\\\\%\\[\\]_]', '\\\\\g<0>',
value.lower())
query = query.filter(
func.lower(field).contains(escaped_value, escape='\\'))
else:
query = query.filter(field == value)
count = query.count()
if order_by is not None:
if self.is_relation(entity, order_by):
mapper = class_mapper(entity)
class_ = None
for prop in mapper.iterate_properties:
try:
class_ = prop.mapper.class_
except (AttributeError, KeyError):
pass
query = self.session.query(entity).join(order_by)
f = self.get_view_field_name(class_, field_names)
field = self.get_field(class_, f)
else:
field = self.get_field(entity, order_by)
if desc:
field = _desc(field)
query = query.order_by(field)
if offset is not None:
query = query.offset(offset)
if limit is not None:
query = query.limit(limit)
objs = query.all()
return count, objs
