def get_list(self, page, sort_column, sort_desc, search, filters, execute=True): """ Return models from the database. :param page: Page number :param sort_column: Sort column name :param sort_desc: Descending or ascending sort :param search: Search query :param execute: Execute query immediately? Default is `True` :param filters: List of filter tuples """ # Will contain names of joined tables to avoid duplicate joins joins = set() query = self.get_query() count_query = self.get_count_query() # Apply search criteria if self._search_supported and search: # Apply search-related joins if self._search_joins: for jn in self._search_joins.values(): query = query.join(jn) count_query = count_query.join(jn) joins = set(self._search_joins.keys()) # Apply terms terms = search.split(' ') for term in terms: if not term: continue stmt = tools.parse_like_term(term) filter_stmt = [c.ilike(stmt) for c in self._search_fields] query = query.filter(or_(*filter_stmt)) count_query = count_query.filter(or_(*filter_stmt)) # Apply filters if filters and self._filters: for idx, value in filters: flt = self._filters[idx] # Figure out joins tbl = flt.column.table.name join_tables = self._filter_joins.get(tbl, []) for table in join_tables: if table.name not in joins: query = query.join(table) count_query = count_query.join(table) joins.add(table.name) # Apply filter query = flt.apply(query, value) count_query = flt.apply(count_query, value) # Calculate number of rows count = count_query.scalar() # Auto join for j in self._auto_joins: query = query.options(joinedload(j)) # Sorting if sort_column is not None: if sort_column in self._sortable_columns: sort_field = self._sortable_columns[sort_column] query, joins = self._order_by(query, joins, sort_field, sort_desc) else: order = get_default_order(self) if order: query, joins = self._order_by(query, joins, order[0], order[1]) # Pagination if page is not None: query = query.offset(page * self.page_size) query = query.limit(self.page_size) # Execute if needed if execute: query = query.all() return count, query
def get_list(self, page, sort_column, sort_desc, search, filters, execute=True): """ Return models from the database. :param page: Page number :param sort_column: Sort column name :param sort_desc: Descending or ascending sort :param search: Search query :param execute: Execute query immediately? Default is `True` :param filters: List of filter tuples """ # Will contain names of joined tables to avoid duplicate joins joins = set() query = self.get_query() # Apply search criteria if self._search_supported and search: # Apply search-related joins if self._search_joins: query = query.join(*self._search_joins.values()) joins = set(self._search_joins.keys()) # Apply terms terms = search.split(' ') for term in terms: if not term: continue stmt = tools.parse_like_term(term) filter_stmt = [c.ilike(stmt) for c in self._search_fields] query = query.filter(or_(*filter_stmt)) # Apply filters if self._filters: # Apply search-related joins if self._filter_joins: new_joins = set(self._filter_joins.keys()) - joins if new_joins: query = query.join(*[self._filter_joins[jn] for jn in new_joins]) joins |= new_joins # Apply filters for flt, value in filters: query = self._filters[flt].apply(query, value) # Calculate number of rows count = query.count() # Auto join for j in self._auto_joins: query = query.options(subqueryload(j)) # Sorting if sort_column is not None: if sort_column in self._sortable_columns: sort_field = self._sortable_columns[sort_column] # Try to handle it as a string if isinstance(sort_field, basestring): # Create automatic join against a table if column name # contains dot. if '.' in sort_field: parts = sort_field.split('.', 1) if parts[0] not in joins: query = query.join(parts[0]) joins.add(parts[0]) elif isinstance(sort_field, InstrumentedAttribute): table = sort_field.parententity.tables[0] if table.name not in joins: query = query.join(table) joins.add(table.name) elif isinstance(sort_field, Column): pass else: raise TypeError('Wrong argument type') if sort_field is not None: if sort_desc: query = query.order_by(desc(sort_field)) else: query = query.order_by(sort_field) # Pagination if page is not None: query = query.offset(page * self.page_size) query = query.limit(self.page_size) # Execute if needed if execute: query = query.all() return count, query
def get_list(self, page, sort_column, sort_desc, search, filters, execute=True): """ Return models from the database. :param page: Page number :param sort_column: Sort column name :param sort_desc: Descending or ascending sort :param search: Search query :param execute: Execute query immediately? Default is `True` :param filters: List of filter tuples """ # Will contain names of joined tables to avoid duplicate joins joins = set() query = self.get_query() count_query = self.get_count_query() # Apply search criteria if self._search_supported and search: # Apply search-related joins if self._search_joins: for jn in self._search_joins.values(): query = query.join(jn) count_query = count_query.join(jn) joins = set(self._search_joins.keys()) # Apply terms terms = search.split(' ') for term in terms: if not term: continue stmt = tools.parse_like_term(term) filter_stmt = [c.ilike(stmt) for c in self._search_fields] query = query.filter(or_(*filter_stmt)) count_query = count_query.filter(or_(*filter_stmt)) # Apply filters if filters and self._filters: for idx, value in filters: flt = self._filters[idx] # Figure out joins tbl = flt.column.table.name join_tables = self._filter_joins.get(tbl, []) for table in join_tables: if table.name not in joins: query = query.join(table) count_query = count_query.join(table) joins.add(table.name) # Apply filter query = flt.apply(query, value) count_query = flt.apply(count_query, value) # Calculate number of rows count = count_query.scalar() # Auto join for j in self._auto_joins: query = query.options(subqueryload(j)) # Sorting if sort_column is not None: if sort_column in self._sortable_columns: sort_field = self._sortable_columns[sort_column] # TODO: Preprocessing for joins # Try to handle it as a string if isinstance(sort_field, basestring): # Create automatic join against a table if column name # contains dot. if '.' in sort_field: parts = sort_field.split('.', 1) if parts[0] not in joins: query = query.join(parts[0]) joins.add(parts[0]) elif isinstance(sort_field, InstrumentedAttribute): # SQLAlchemy 0.8+ uses 'parent' as a name mapper = getattr(sort_field, 'parent', None) if mapper is None: # SQLAlchemy 0.7.x uses parententity mapper = getattr(sort_field, 'parententity', None) if mapper is not None: table = mapper.tables[0] if table.name not in joins: query = query.join(table) joins.add(table.name) elif isinstance(sort_field, Column): pass else: raise TypeError('Wrong argument type') if sort_field is not None: if sort_desc: query = query.order_by(desc(sort_field)) else: query = query.order_by(sort_field) # Pagination if page is not None: query = query.offset(page * self.page_size) query = query.limit(self.page_size) # Execute if needed if execute: query = query.all() return count, query
def apply(self, query, value): stmt = tools.parse_like_term(value) return query.filter(~self.column.ilike(stmt))
def get_list(self, page, sort_column, sort_desc, search, filters, execute=True): """ Return models from the database. :param page: Page number :param sort_column: Sort column name :param sort_desc: Descending or ascending sort :param search: Search query :param execute: Execute query immediately? Default is `True` :param filters: List of filter tuples """ # Will contain names of joined tables to avoid duplicate joins joins = set() query = self.get_query() # Apply search criteria if self._search_supported and search: # Apply search-related joins if self._search_joins: for jn in self._search_joins.values(): query = query.join(jn) joins = set(self._search_joins.keys()) # Apply terms terms = search.split(' ') for term in terms: if not term: continue stmt = tools.parse_like_term(term) filter_stmt = [c.ilike(stmt) for c in self._search_fields] query = query.filter(or_(*filter_stmt)) # Apply filters if filters and self._filters: for idx, value in filters: flt = self._filters[idx] # Figure out join tbl = flt.column.table.name join = self._filter_joins.get(tbl) if join is not None: query = query.join(join) joins.add(tbl) # Apply filter query = flt.apply(query, value) # Calculate number of rows count = query.count() # Auto join for j in self._auto_joins: query = query.options(subqueryload(j)) # Sorting if sort_column is not None: if sort_column in self._sortable_columns: sort_field = self._sortable_columns[sort_column] # Try to handle it as a string if isinstance(sort_field, basestring): # Create automatic join against a table if column name # contains dot. if '.' in sort_field: parts = sort_field.split('.', 1) if parts[0] not in joins: query = query.join(parts[0]) joins.add(parts[0]) elif isinstance(sort_field, InstrumentedAttribute): table = sort_field.parententity.tables[0] if table.name not in joins: query = query.join(table) joins.add(table.name) elif isinstance(sort_field, Column): pass else: raise TypeError('Wrong argument type') if sort_field is not None: if sort_desc: query = query.order_by(desc(sort_field)) else: query = query.order_by(sort_field) # Pagination if page is not None: query = query.offset(page * self.page_size) query = query.limit(self.page_size) # Execute if needed if execute: query = query.all() return count, query