def apply(self, query, value): stmt = tools.parse_like_term(value) return query.join( (ContactInfo, Order.shipping_info_id == ContactInfo.id )).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() 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 = self._get_default_order() 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 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() 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 = self._get_default_order() 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, user_id=None): """ 得到过滤的视图 """ # Will contain names of joined tables to avoid duplicate joins joins = set() query = self.get_query() count_query = self.get_count_query() if user_id is None: flash(u'通过会员详情查看', 'error') query = query.filter(UserTicket.user_id == '-1') # 不显示任何的优惠券信息 else: query = query.filter(UserTicket.user_id == user_id) if not self.valid_user_manager(user_id): flash(u'系统错误,不存在本用户', 'error') query = query.filter(UserTicket.user_id == '-1') # 不显示任何的优惠券信息 # 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 = self._get_default_order() 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 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() count_query = self.get_count_query() # Ignore eager-loaded relations (prevent unnecessary joins) # TODO: Separate join detection for query and count query? if hasattr(query, '_join_entities'): for entity in query._join_entities: for table in entity.tables: joins.add(table.name) # Apply search criteria if self._search_supported and search: # Apply search-related joins if self._search_joins: for table in self._search_joins: if table.name not in joins: query = query.outerjoin(table) count_query = count_query.outerjoin(table) joins.add(table.name) # 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, flt_name, 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) # turn into python format with .clean() and apply filter query = flt.apply(query, flt.clean(value)) count_query = flt.apply(count_query, flt.clean(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] sort_joins = self._sortable_joins.get(sort_column) query, joins = self._order_by(query, joins, sort_joins, sort_field, sort_desc) else: order = self._get_default_order() if order: sort_joins, sort_field, sort_desc = order query, joins = self._order_by(query, joins, sort_joins, sort_field, sort_desc) # 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): self.page_size = page_size_parser.parse_args()['page_size'] 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] if not value: continue # 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 = self._get_default_order() if order: query, joins = self._order_by(query, joins, order[0], order[1]) if self.get_match_all_query: self.get_match_all_query = None self.get_match_all_query = query # 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() # Ignore eager-loaded relations (prevent unnecessary joins) # TODO: Separate join detection for query and count query? if hasattr(query, '_join_entities'): for entity in query._join_entities: for table in entity.tables: joins.add(table.name) # Apply search criteria if self._search_supported and search: # Apply search-related joins if self._search_joins: for table in self._search_joins: if table.name not in joins: query = query.outerjoin(table) count_query = count_query.outerjoin(table) joins.add(table.name) # 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, flt_name, 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) # turn into python format with .clean() and apply filter query = flt.apply(query, flt.clean(value)) count_query = flt.apply(count_query, flt.clean(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] sort_joins = self._sortable_joins.get(sort_column) query, joins = self._order_by(query, joins, sort_joins, sort_field, sort_desc) else: order = self._get_default_order() if order: sort_joins, sort_field, sort_desc = order query, joins = self._order_by(query, joins, sort_joins, sort_field, sort_desc) # 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