def buildQuery(sqlQuery, query, mapped): ''' Builds the query on the SQL alchemy query. @param sqlQuery: SQL alchemy The sql alchemy query to use. @param query: query The REST query object to provide filtering on. @param mapped: class The mapped model class to use the query on. ''' assert query is not None, 'A query object is required' clazz = query.__class__ ordered, unordered = [], [] properties = {prop.lower(): getattr(mapped, prop) for prop in namesForModel(mapped)} for criteria in namesForQuery(clazz): column = properties.get(criteria.lower()) if column is not None and getattr(clazz, criteria) in query: crt = getattr(query, criteria) if isinstance(crt, AsBoolean): assert isinstance(crt, AsBoolean) if AsBoolean.value in crt: sqlQuery = sqlQuery.filter(column == crt.value) elif isinstance(crt, AsLike): assert isinstance(crt, AsLike) if AsLike.like in crt: sqlQuery = sqlQuery.filter(column.like(crt.like)) elif AsLike.ilike in crt: sqlQuery = sqlQuery.filter(column.ilike(crt.ilike)) elif isinstance(crt, AsEqual): assert isinstance(crt, AsEqual) if AsEqual.equal in crt: sqlQuery = sqlQuery.filter(column == crt.equal) elif isinstance(crt, (AsDate, AsTime, AsDateTime, AsRange)): if crt.__class__.start in crt: sqlQuery = sqlQuery.filter(column >= crt.start) elif crt.__class__.until in crt: sqlQuery = sqlQuery.filter(column < crt.until) if crt.__class__.end in crt: sqlQuery = sqlQuery.filter(column <= crt.end) elif crt.__class__.since in crt: sqlQuery = sqlQuery.filter(column > crt.since) if isinstance(crt, AsOrdered): assert isinstance(crt, AsOrdered) if AsOrdered.ascending in crt: if AsOrdered.priority in crt and crt.priority: ordered.append((column, crt.ascending, crt.priority)) else: unordered.append((column, crt.ascending, None)) ordered.sort(key=lambda pack: pack[2]) for column, asc, __ in chain(ordered, unordered): if asc: sqlQuery = sqlQuery.order_by(column) else: sqlQuery = sqlQuery.order_by(column.desc()) return sqlQuery
def buildQuery(sqlQuery, query, mapped, only=None, exclude=None): ''' Builds the query on the SQL alchemy query. @param sqlQuery: SQL alchemy The sql alchemy query to use. @param query: query The REST query object to provide filtering on. @param mapped: class The mapped model class to use the query on. @param only: tuple(string|TypeCriteriaEntry)|string|TypeCriteriaEntry|None The criteria names or references to build the query for, if no criteria is provided then all the query criteria are considered. @param exclude: tuple(string|TypeCriteriaEntry)|string|TypeCriteriaEntry|None The criteria names or references to be excluded when processing the query. If you provided a only parameter you cannot provide an exclude. ''' assert query is not None, 'A query object is required' clazz = query.__class__ columns, ordered, unordered = {}, [], [] for name in namesForModel(mapped): cp, name = getattr(mapped, name), name.lower() if name not in columns and isinstance(cp, (PropertyAttribute, _Case)): columns[name] = cp columns = { criteria: columns.get(criteria.lower()) for criteria in namesForQuery(clazz) } if only: if not isinstance(only, tuple): only = (only, ) assert not exclude, 'Cannot have only \'%s\' and exclude \'%s\' criteria at the same time' % ( only, exclude) onlyColumns = {} for criteria in only: if isinstance(criteria, str): column = columns.get(criteria) assert column is not None, 'Invalid only criteria name \'%s\' for query class %s' % ( criteria, clazz) onlyColumns[criteria] = column else: typ = typeFor(criteria) assert isinstance( typ, TypeCriteriaEntry), 'Invalid only criteria %s' % criteria column = columns.get(criteria) assert column is not None, 'Invalid only criteria \'%s\' for query class %s' % ( criteria, clazz) onlyColumns[criteria] = column columns = onlyColumns elif exclude: if not isinstance(exclude, tuple): exclude = (exclude, ) for criteria in exclude: if isinstance(criteria, str): column = columns.pop(criteria, None) assert column is not None, 'Invalid exclude criteria name \'%s\' for query class %s' % ( criteria, clazz) else: typ = typeFor(criteria) assert isinstance(typ, TypeCriteriaEntry ), 'Invalid exclude criteria %s' % criteria column = columns.pop(typ.name, None) assert column is not None, 'Invalid exclude criteria \'%s\' for query class %s' % ( criteria, clazz) for criteria, column in columns.items(): if column is None or getattr(clazz, criteria) not in query: continue crt = getattr(query, criteria) if isinstance(crt, AsBoolean): assert isinstance(crt, AsBoolean) if AsBoolean.value in crt: sqlQuery = sqlQuery.filter(column == crt.value) elif isinstance(crt, AsLike): assert isinstance(crt, AsLike) if AsLike.like in crt: sqlQuery = sqlQuery.filter(column.like(crt.like)) elif AsLike.ilike in crt: sqlQuery = sqlQuery.filter(column.ilike(crt.ilike)) elif isinstance(crt, AsEqual): assert isinstance(crt, AsEqual) if AsEqual.equal in crt: sqlQuery = sqlQuery.filter(column == crt.equal) elif isinstance(crt, (AsDate, AsTime, AsDateTime, AsRange)): if crt.__class__.start in crt: sqlQuery = sqlQuery.filter(column >= crt.start) elif crt.__class__.until in crt: sqlQuery = sqlQuery.filter(column < crt.until) if crt.__class__.end in crt: sqlQuery = sqlQuery.filter(column <= crt.end) elif crt.__class__.since in crt: sqlQuery = sqlQuery.filter(column > crt.since) if isinstance(crt, AsOrdered): assert isinstance(crt, AsOrdered) if AsOrdered.ascending in crt: if AsOrdered.priority in crt and crt.priority: ordered.append((column, crt.ascending, crt.priority)) else: unordered.append((column, crt.ascending, None)) ordered.sort(key=lambda pack: pack[2]) for column, asc, __ in chain(ordered, unordered): if asc: sqlQuery = sqlQuery.order_by(column) else: sqlQuery = sqlQuery.order_by(column.desc()) return sqlQuery
def buildQuery(sqlQuery, query, mapped, only=None, exclude=None): ''' Builds the query on the SQL alchemy query. @param sqlQuery: SQL alchemy The sql alchemy query to use. @param query: query The REST query object to provide filtering on. @param mapped: class The mapped model class to use the query on. @param only: tuple(string|TypeCriteriaEntry)|string|TypeCriteriaEntry|None The criteria names or references to build the query for, if no criteria is provided then all the query criteria are considered. @param exclude: tuple(string|TypeCriteriaEntry)|string|TypeCriteriaEntry|None The criteria names or references to be excluded when processing the query. If you provided a only parameter you cannot provide an exclude. ''' assert query is not None, 'A query object is required' clazz = query.__class__ columns, ordered, unordered = {}, [], [] for name in namesForModel(mapped): cp, name = getattr(mapped, name), name.lower() if name not in columns and isinstance(cp, (PropertyAttribute, _Case)): columns[name] = cp columns = {criteria:columns.get(criteria.lower()) for criteria in namesForQuery(clazz)} if only: if not isinstance(only, tuple): only = (only,) assert not exclude, 'Cannot have only \'%s\' and exclude \'%s\' criteria at the same time' % (only, exclude) onlyColumns = {} for criteria in only: if isinstance(criteria, str): column = columns.get(criteria) assert column is not None, 'Invalid only criteria name \'%s\' for query class %s' % (criteria, clazz) onlyColumns[criteria] = column else: typ = typeFor(criteria) assert isinstance(typ, TypeCriteriaEntry), 'Invalid only criteria %s' % criteria column = columns.get(criteria) assert column is not None, 'Invalid only criteria \'%s\' for query class %s' % (criteria, clazz) onlyColumns[criteria] = column columns = onlyColumns elif exclude: if not isinstance(exclude, tuple): exclude = (exclude,) for criteria in exclude: if isinstance(criteria, str): column = columns.pop(criteria, None) assert column is not None, 'Invalid exclude criteria name \'%s\' for query class %s' % (criteria, clazz) else: typ = typeFor(criteria) assert isinstance(typ, TypeCriteriaEntry), 'Invalid exclude criteria %s' % criteria column = columns.pop(typ.name, None) assert column is not None, 'Invalid exclude criteria \'%s\' for query class %s' % (criteria, clazz) for criteria, column in columns.items(): if column is None or getattr(clazz, criteria) not in query: continue crt = getattr(query, criteria) if isinstance(crt, AsBoolean): assert isinstance(crt, AsBoolean) if AsBoolean.value in crt: sqlQuery = sqlQuery.filter(column == crt.value) elif isinstance(crt, AsLike): assert isinstance(crt, AsLike) if AsLike.like in crt: sqlQuery = sqlQuery.filter(column.like(crt.like)) elif AsLike.ilike in crt: sqlQuery = sqlQuery.filter(column.ilike(crt.ilike)) elif isinstance(crt, AsEqual): assert isinstance(crt, AsEqual) if AsEqual.equal in crt: sqlQuery = sqlQuery.filter(column == crt.equal) elif isinstance(crt, (AsDate, AsTime, AsDateTime, AsRange)): if crt.__class__.start in crt: sqlQuery = sqlQuery.filter(column >= crt.start) elif crt.__class__.until in crt: sqlQuery = sqlQuery.filter(column < crt.until) if crt.__class__.end in crt: sqlQuery = sqlQuery.filter(column <= crt.end) elif crt.__class__.since in crt: sqlQuery = sqlQuery.filter(column > crt.since) if isinstance(crt, AsOrdered): assert isinstance(crt, AsOrdered) if AsOrdered.ascending in crt: if AsOrdered.priority in crt and crt.priority: ordered.append((column, crt.ascending, crt.priority)) else: unordered.append((column, crt.ascending, None)) ordered.sort(key=lambda pack: pack[2]) for column, asc, __ in chain(ordered, unordered): if asc: sqlQuery = sqlQuery.order_by(column) else: sqlQuery = sqlQuery.order_by(column.desc()) return sqlQuery