def get_order_by(self): """ Returns a list of 2-tuples of form (expr, (sql, params)) for the ORDER BY clause. The order_by clause can alter the select clause (for example it can add aliases to clauses that do not yet have one, or it can add totally new select clauses). """ if self.query.extra_order_by: ordering = self.query.extra_order_by elif not self.query.default_ordering: ordering = self.query.order_by else: ordering = self.query.order_by or self.query.get_meta().ordering or [] if self.query.standard_ordering: asc, desc = ORDER_DIR["ASC"] else: asc, desc = ORDER_DIR["DESC"] order_by = [] for pos, field in enumerate(ordering): if field == "?": order_by.append((Random(), asc, False)) continue if isinstance(field, int): if field < 0: field = -field int_ord = desc order_by.append((ColIndexRef(field), int_ord, True)) continue col, order = get_order_dir(field, asc) if col in self.query.annotation_select: order_by.append((Ref(col, self.query.annotation_select[col]), order, True)) continue if "." in field: # This came in through an extra(order_by=...) addition. Pass it # on verbatim. table, col = col.split(".", 1) expr = RawSQL("%s.%s" % (self.quote_name_unless_alias(table), col), []) order_by.append((expr, order, False)) continue if not self.query._extra or get_order_dir(field)[0] not in self.query._extra: # 'col' is of the form 'field' or 'field1__field2' or # '-field1__field2__field', etc. order_by.extend(self.find_ordering_name(field, self.query.get_meta(), default_order=asc)) else: if col not in self.query.extra_select: order_by.append((RawSQL(*self.query.extra[col]), order, False)) else: order_by.append((Ref(col, RawSQL(*self.query.extra[col])), order, True)) result = [] seen = set() for expr, order, is_ref in order_by: sql, params = self.compile(expr) if (sql, tuple(params)) in seen: continue seen.add((sql, tuple(params))) result.append((expr, (sql, params, order, is_ref))) return result
def find_ordering_name(self, name, opts, alias=None, default_order='ASC', already_seen=None): """ Returns the table alias (the name might be ambiguous, the alias will not be) and column name for ordering by the given 'name' parameter. The 'name' is of the form 'field1__field2__...__fieldN'. """ name, order = get_order_dir(name, default_order) descending = True if order == 'DESC' else False pieces = name.split(LOOKUP_SEP) field, targets, alias, joins, path, opts = self._setup_joins(pieces, opts, alias) # If we get to this point and the field is a relation to another model, # append the default ordering for that model unless the attribute name # of the field is specified. if field.rel and path and opts.ordering and name != field.attname: # Firstly, avoid infinite loops. if not already_seen: already_seen = set() join_tuple = tuple(self.query.alias_map[j].table_name for j in joins) if join_tuple in already_seen: raise FieldError('Infinite loop caused by ordering.') already_seen.add(join_tuple) results = [] for item in opts.ordering: results.extend(self.find_ordering_name(item, opts, alias, order, already_seen)) return results targets, alias, _ = self.query.trim_joins(targets, joins, path) return [(OrderBy(t.get_col(alias), descending=descending), False) for t in targets]
def find_ordering_name(self, name, opts, alias=None, default_order='ASC', already_seen=None): """ Returns the table alias (the name might be ambiguous, the alias will not be) and column name for ordering by the given 'name' parameter. The 'name' is of the form 'field1__field2__...__fieldN'. """ name, order = get_order_dir(name, default_order) pieces = name.split(LOOKUP_SEP) field, col, alias, joins, opts = self._setup_joins(pieces, opts, alias) # If we get to this point and the field is a relation to another model, # append the default ordering for that model. if field.rel and len(joins) > 1 and opts.ordering: # Firstly, avoid infinite loops. if not already_seen: already_seen = set() join_tuple = tuple( [self.query.alias_map[j].table_name for j in joins]) if join_tuple in already_seen: raise FieldError('Infinite loop caused by ordering.') already_seen.add(join_tuple) results = [] for item in opts.ordering: results.extend( self.find_ordering_name(item, opts, alias, order, already_seen)) return results col, alias = self._final_join_removal(col, alias) return [(alias, col, order)]
def find_ordering_name(self, name, opts, alias=None, default_order='ASC', already_seen=None): """ Returns the table alias (the name might be ambiguous, the alias will not be) and column name for ordering by the given 'name' parameter. The 'name' is of the form 'field1__field2__...__fieldN'. """ name, order = get_order_dir(name, default_order) descending = True if order == 'DESC' else False pieces = name.split(LOOKUP_SEP) field, targets, alias, joins, path, opts = self._setup_joins(pieces, opts, alias) # If we get to this point and the field is a relation to another model, # append the default ordering for that model unless the attribute name # of the field is specified. if field.rel and path and opts.ordering and name != field.attname: # Firstly, avoid infinite loops. if not already_seen: already_seen = set() join_tuple = tuple(self.query.alias_map[j].table_name for j in joins) if join_tuple in already_seen: raise FieldError('Infinite loop caused by ordering.') already_seen.add(join_tuple) results = [] for item in opts.ordering: results.extend(self.find_ordering_name(item, opts, alias, order, already_seen)) return results targets, alias, _ = self.query.trim_joins(targets, joins, path) return [(OrderBy(t.get_col(alias), descending=descending), False) for t in targets]
def get_order_by(self): cdms_orderby = [] if self.query.order_by: ordering = self.query.order_by else: ordering = self.query.model._meta.ordering if not ordering: warnings.warn( "{0} does not have a default ordering so cdms calls and local ones " "are not similarly ordered. We strongly recommend you should add " "a Meta class with ordering = ['modified'] or similar.".format(self.query.model.__name__) ) ordering = ['modified'] for field in ordering: col, order = get_order_dir(field, 'ASC') try: cdms_field = self.get_migrator().get_cdms_field(col) except NotMappingFieldException: raise NotImplementedError( 'Cannot order by {0}, only ordering by direct fields currently implemented'.format(col) ) cdms_orderby.append( '{0} {1}'.format(cdms_field.cdms_name, order.lower()) ) return cdms_orderby
def find_ordering_name(self, name, opts, alias=None, default_order='ASC', already_seen=None): """ Returns the table alias (the name might be ambiguous, the alias will not be) and column name for ordering by the given 'name' parameter. The 'name' is of the form 'field1__field2__...__fieldN'. """ name, order = get_order_dir(name, default_order) pieces = name.split(LOOKUP_SEP) field, col, alias, joins, opts = self._setup_joins(pieces, opts, alias) # If we get to this point and the field is a relation to another model, # append the default ordering for that model. if field.rel and len(joins) > 1 and opts.ordering: # Firstly, avoid infinite loops. if not already_seen: already_seen = set() join_tuple = tuple([self.query.alias_map[j].table_name for j in joins]) if join_tuple in already_seen: raise FieldError('Infinite loop caused by ordering.') already_seen.add(join_tuple) results = [] for item in opts.ordering: results.extend(self.find_ordering_name(item, opts, alias, order, already_seen)) return results col, alias = self._final_join_removal(col, alias) return [(alias, col, order)]
def add_ordering(self, *ordering): new_ordering = [] for order in ordering: field_name, dirn = get_order_dir(order) new_name = expand_lookup(self.get_meta(), field_name) if dirn == 'DESC': new_name = '-%s' % new_name new_ordering.append(new_name) return super(MultilingualQuery, self).add_ordering(*new_ordering)
def get_group_ordering(self): group_order_by = getattr(self.query, 'group_order_by', ()) asc, desc = ORDER_DIR['ASC'] if not group_order_by: return '' result = [] for order_by in group_order_by: col, order = get_order_dir(order_by, asc) result.append("%s %s" % (col, order)) return " WITHIN GROUP ORDER BY " + ", ".join(result)
def get_group_ordering(self): group_order_by = getattr(self.query, 'group_order_by', ()) asc, desc = ORDER_DIR['ASC'] if not group_order_by: return '' result = [] for order_by in group_order_by: col, order = get_order_dir(order_by, asc) result.append("%s %s" % (col, order)) return " WITHIN GROUP ORDER BY " + ", ".join(result)
def get_next_or_previous(qs, item, next=True): """ Get the next or previous object in the queryset, with regards to the item specified. """ # If we want the previous object, reverse the default ordering if next: default_ordering = 'ASC' else: default_ordering = 'DESC' # First, determine the ordering. This code is from get_ordering() in # django.db.sql.compiler if qs.query.extra_order_by: ordering = qs.query.extra_order_by elif not qs.query.default_ordering: ordering = qs.query.order_by else: ordering = qs.query.order_by or qs.query.model._meta.ordering assert not ordering == '?', 'This makes no sense for random ordering.' query_filter = None for field in ordering: item_value = getattr(item, field) # Account for possible reverse ordering field, direction = get_order_dir(field, default_ordering) # Either make sure we filter increased values or lesser values # depending on the sort order if direction == 'ASC': filter_dict = {'%s__gt' % field: item_value} else: filter_dict = {'%s__lt' % field: item_value} # Make sure we nicely or the conditions for the queryset if query_filter: query_filter = query_filter | Q(**filter_dict) else: query_filter = Q(**filter_dict) # Reverse the order if we're looking for previous items if default_ordering == 'DESC': qs = qs.reverse() # Filter the queryset qs = qs.filter(query_filter) # Return either the next/previous item or None if not existent try: return qs[0] except IndexError: return None
def find_ordering_name(self, name, opts, alias=None, default_order='ASC', already_seen=None): """ Returns the table alias (the name might be ambiguous, the alias will not be) and column name for ordering by the given 'name' parameter. The 'name' is of the form 'field1__field2__...__fieldN'. """ name, order = get_order_dir(name, default_order) pieces = name.split(LOOKUP_SEP) if not alias: alias = self.query.get_initial_alias() field, target, opts, joins, last, extra = self.query.setup_joins(pieces, opts, alias, False) alias = joins[-1] col = target.column if not field.rel: # To avoid inadvertent trimming of a necessary alias, use the # refcount to show that we are referencing a non-relation field on # the model. self.query.ref_alias(alias) # Must use left outer joins for nullable fields and their relations. self.query.promote_alias_chain(joins, self.query.alias_map[joins[0]][JOIN_TYPE] == self.query.LOUTER) # If we get to this point and the field is a relation to another model, # append the default ordering for that model. if field.rel and len(joins) > 1 and opts.ordering: # Firstly, avoid infinite loops. if not already_seen: already_seen = set() join_tuple = tuple([self.query.alias_map[j][TABLE_NAME] for j in joins]) if join_tuple in already_seen: raise FieldError('Infinite loop caused by ordering.') already_seen.add(join_tuple) results = [] for item in opts.ordering: results.extend(self.find_ordering_name(item, opts, alias, order, already_seen)) return results if alias: # We have to do the same "final join" optimisation as in # add_filter, since the final column might not otherwise be part of # the select set (so we can't order on it). while 1: join = self.query.alias_map[alias] if col != join[RHS_JOIN_COL]: break self.query.unref_alias(alias) alias = join[LHS_ALIAS] col = join[LHS_JOIN_COL] return [(alias, col, order)]
def get_next_or_previous(qs, item, next=True): """ Get the next or previous object in the queryset, with regards to the item specified. """ # If we want the previous object, reverse the default ordering if next: default_ordering = 'ASC' else: default_ordering = 'DESC' # First, determine the ordering. This code is from get_ordering() in # django.db.sql.compiler if qs.query.extra_order_by: ordering = qs.query.extra_order_by elif not qs.query.default_ordering: ordering = qs.query.order_by else: ordering = qs.query.order_by or qs.query.model._meta.ordering assert not ordering == '?', 'This makes no sense for random ordering.' query_filter = None for field in ordering: item_value = getattr(item, field) # Account for possible reverse ordering field, direction = get_order_dir(field, default_ordering) # Either make sure we filter increased values or lesser values # depending on the sort order if direction == 'ASC': filter_dict = {'%s__gt' % field: item_value} else: filter_dict = {'%s__lt' % field: item_value} # Make sure we nicely or the conditions for the queryset if query_filter: query_filter = query_filter | Q(**filter_dict) else: query_filter = Q(**filter_dict) # Reverse the order if we're looking for previous items if default_ordering == 'DESC': qs = qs.reverse() # Filter the queryset qs = qs.filter(query_filter) # Return either the next/previous item or None if not existent try: return qs[0] except IndexError: return None
def find_ordering_name(self, name, opts, alias=None, default_order='ASC', already_seen=None): """ Returns the table alias (the name might be ambiguous, the alias will not be) and column name for ordering by the given 'name' parameter. The 'name' is of the form 'field1__field2__...__fieldN'. """ name, order = get_order_dir(name, default_order) pieces = name.split(LOOKUP_SEP) if not alias: alias = self.query.get_initial_alias() field, target, opts, joins, last, extra = self.query.setup_joins(pieces, opts, alias, False) alias = joins[-1] col = target.column if not field.rel: # To avoid inadvertent trimming of a necessary alias, use the # refcount to show that we are referencing a non-relation field on # the model. self.query.ref_alias(alias) # Must use left outer joins for nullable fields and their relations. self.query.promote_alias_chain(joins, self.query.alias_map[joins[0]][JOIN_TYPE] == self.query.LOUTER) # If we get to this point and the field is a relation to another model, # append the default ordering for that model. if field.rel and len(joins) > 1 and opts.ordering: # Firstly, avoid infinite loops. if not already_seen: already_seen = set() join_tuple = tuple([self.query.alias_map[j][TABLE_NAME] for j in joins]) if join_tuple in already_seen: raise FieldError('Infinite loop caused by ordering.') already_seen.add(join_tuple) results = [] for item in opts.ordering: results.extend(self.find_ordering_name(item, opts, alias, order, already_seen)) return results if alias: # We have to do the same "final join" optimisation as in # add_filter, since the final column might not otherwise be part of # the select set (so we can't order on it). while 1: join = self.query.alias_map[alias] if col != join[RHS_JOIN_COL]: break self.query.unref_alias(alias) alias = join[LHS_ALIAS] col = join[LHS_JOIN_COL] return [(alias, col, order)]
def order_with_nulls(queryset, field): """ If sorting order is ascending, then NULL values come first, if sorting order is descending, then NULL values come last. """ col, order = get_order_dir(field) descending = True if order == 'DESC' else False if descending: return queryset.order_by(F(col).desc(nulls_last=True)) else: return queryset.order_by(F(col).asc(nulls_first=True))
def get_neighbour(self, instance, successor=True): """ Get the neighbour of an object in the queryset """ # First, determine the ordering. This code is from get_ordering() in # django.db.sql.compiler if self.query.extra_order_by: ordering = self.query.extra_order_by elif not self.query.default_ordering: ordering = self.query.order_by else: ordering = self.query.order_by or self.query.model._meta.ordering assert '?' not in ordering, 'This makes no sense for random ordering.' # If we want the previous object, reverse the default ordering default_ordering = 'ASC' if successor else 'DESC' ordering_map = {'ASC': 'gt', 'DESC': 'lt'} # To find the sucessor, we need to construct a filter such that # (replacing greater with lesser as appropriate): # # * The first value is greater than the instance value, or # * The first value is the same, but the second is greater, or # * The first n-1 values are the same, but the nth value is greater # # ``q`` holds the final ``Q`` instance used for filtering, and is # built up each iteration. ``same_q`` holds a ``Q`` value used to # ensure the ``n-1`` previous values are the same for the ``nth`` # field q = Q() same_q = Q() for field in ordering: item_value = reduce(getattr, field.split('__'), instance) field, direction = get_order_dir(field, default_ordering) condition = '{0}__{1}'.format(field, ordering_map[direction]) q = q | (same_q & Q(**{condition: item_value})) same_q = same_q & Q(**{field: item_value}) # Construct a new QuerySet to find the neighbour qs = self.filter(q) # Reverse the order if we're looking for the predecessor if not successor: qs = qs.reverse() # Return the neighbour, or None if it does not exist try: return qs[0] except IndexError: return None
def find_ordering_name(self, name, opts, alias=None, default_order='ASC', already_seen=None): name, order = get_order_dir(name, default_order) pieces = name.split(LOOKUP_SEP) ## TODO: othermodel__joined_dictfield__subfield try: if isinstance(self.query.model._meta.get_field(pieces[0]), DictField): descending = order == 'DESC' return [(CrateOrderBy(pieces, descending=descending), False)] except FieldDoesNotExist: ## pk field pass return super().find_ordering_name(name, opts, alias, order, already_seen)
def find_ordering_name(self, name, opts, alias=None, default_order="ASC", already_seen=None): """ Overridden just for the __scatter__ property ordering """ # This allow special appengine properties (e.g. __scatter__) to be supplied as an ordering # even though they don't (and can't) exist as Django model fields if name.startswith("__") and name.endswith("__"): name, order = get_order_dir(name, default_order) descending = True if order == "DESC" else False return [(OrderBy(Value("__scatter__"), descending=descending), False)] return super(SQLCompiler, self).find_ordering_name( name, opts, alias=alias, default_order=default_order, already_seen=already_seen )
def get_group_ordering(self): """ Returns group ordering clause. Formats WITHIN GROUP ORDER BY expression with columns in query.group_order_by """ group_order_by = getattr(self.query, 'group_order_by', ()) asc, desc = ORDER_DIR['ASC'] if not group_order_by: return '' result = [] for order_by in group_order_by: col, order = get_order_dir(order_by, asc) result.append("%s %s" % (col, order)) return " WITHIN GROUP ORDER BY " + ", ".join(result)
def get_group_ordering(self): """ Returns group ordering clause. Formats WITHIN GROUP ORDER BY expression with columns in query.group_order_by """ group_order_by = getattr(self.query, 'group_order_by', ()) asc, desc = ORDER_DIR['ASC'] if not group_order_by: return '' result = [] for order_by in group_order_by: col, order = get_order_dir(order_by, asc) result.append("%s %s" % (col, order)) return " WITHIN GROUP ORDER BY " + ", ".join(result)
def find_ordering_name(self, name, opts, alias=None, default_order='ASC', already_seen=None): """ Overridden just for the __scatter__ property ordering """ # This allow special appengine properties (e.g. __scatter__) to be supplied as an ordering # even though they don't (and can't) exist as Django model fields if name.startswith("__") and name.endswith("__"): name, order = get_order_dir(name, default_order) descending = True if order == 'DESC' else False return [(OrderBy(Value('__scatter__'), descending=descending), False)] return super(SQLCompiler, self).find_ordering_name( name, opts, alias=alias, default_order=default_order, already_seen=already_seen )
def find_ordering_name(self, name, opts, alias=None, default_order='ASC', already_seen=None): name, order = get_order_dir(name, default_order) descending = True if order == 'DESC' else False pieces = name.split(LOOKUP_SEP) field, targets, alias, joins, path, opts = self._setup_joins(pieces, opts, alias) if field.is_relation and opts.ordering and getattr(field, 'attname', None) != name: if not already_seen: already_seen = set() join_tuple = tuple(getattr(self.query.alias_map[j], 'join_cols', None) for j in joins) if join_tuple in already_seen: raise FieldError('Infinite loop caused by ordering.') already_seen.add(join_tuple) results = [] for item in opts.ordering: results.extend(self.find_ordering_name(item, opts, alias, order, already_seen)) return results targets, alias, _ = self.query.trim_joins(targets, joins, path) return [(OrderBy(t.get_col(alias), descending=descending), False) for t in targets]
def get_ordering(self): """ Returns a tuple containing a list representing the SQL elements in the "order by" clause, and the list of SQL elements that need to be added to the GROUP BY clause as a result of the ordering. Also sets the ordering_aliases attribute on this instance to a list of extra aliases needed in the select. Determining the ordering SQL can change the tables we need to include, so this should be run *before* get_from_clause(). """ if self.query.extra_order_by: ordering = self.query.extra_order_by elif not self.query.default_ordering: ordering = self.query.order_by else: ordering = (self.query.order_by or self.query.model._meta.ordering or []) qn = self.quote_name_unless_alias qn2 = self.connection.ops.quote_name distinct = self.query.distinct select_aliases = self._select_aliases result = [] group_by = [] ordering_aliases = [] if self.query.standard_ordering: asc, desc = ORDER_DIR['ASC'] else: asc, desc = ORDER_DIR['DESC'] # It's possible, due to model inheritance, that normal usage might try # to include the same field more than once in the ordering. We track # the table/column pairs we use and discard any after the first use. processed_pairs = set() for field in ordering: if field == '?': result.append(self.connection.ops.random_function_sql()) continue if isinstance(field, int): if field < 0: order = desc field = -field else: order = asc result.append('%s %s' % (field, order)) group_by.append((field, [])) continue col, order = get_order_dir(field, asc) if col in self.query.aggregate_select: result.append('%s %s' % (qn(col), order)) continue if '.' in field: # This came in through an extra(order_by=...) addition. Pass it # on verbatim. table, col = col.split('.', 1) if (table, col) not in processed_pairs: elt = '%s.%s' % (qn(table), col) processed_pairs.add((table, col)) if not distinct or elt in select_aliases: result.append('%s %s' % (elt, order)) group_by.append((elt, [])) elif get_order_dir(field)[0] not in self.query.extra_select: # 'col' is of the form 'field' or 'field1__field2' or # '-field1__field2__field', etc. for table, col, order in self.find_ordering_name(field, self.query.model._meta, default_order=asc): if (table, col) not in processed_pairs: elt = '%s.%s' % (qn(table), qn2(col)) processed_pairs.add((table, col)) if distinct and elt not in select_aliases: ordering_aliases.append(elt) result.append('%s %s' % (elt, order)) group_by.append((elt, [])) else: elt = qn2(col) if distinct and col not in select_aliases: ordering_aliases.append(elt) result.append('%s %s' % (elt, order)) group_by.append(self.query.extra_select[col]) self.query.ordering_aliases = ordering_aliases return result, group_by
def get_order_by(self): if self.query.extra_order_by: ordering = self.query.extra_order_by elif not self.query.default_ordering: ordering = self.query.order_by else: ordering = (self.query.order_by or self.query.get_meta().ordering or []) if self.query.standard_ordering: asc, desc = ORDER_DIR['ASC'] else: asc, desc = ORDER_DIR['DESC'] order_by = [] for pos, field in enumerate(ordering): if hasattr(field, 'resolve_expression'): if not isinstance(field, OrderBy): field = field.asc() if not self.query.standard_ordering: field.reverse_ordering() order_by.append((field, False)) continue if field == '?': order_by.append((OrderBy(Random()), False)) continue col, order = get_order_dir(field, asc) descending = True if order == 'DESC' else False if col in self.query.annotation_select: order_by.append(( OrderBy(Ref(col, self.query.annotation_select[col]), descending=descending), True)) continue if col in self.query.annotations: order_by.append(( OrderBy(self.query.annotations[col], descending=descending), False)) continue if '.' in field: table, col = col.split('.', 1) order_by.append(( OrderBy( RawSQL('%s.%s' % (self.quote_name_unless_alias(table), col), []), descending=descending ), False)) continue if not self.query._extra or col not in self.query._extra: order_by.extend(self.find_ordering_name( field, self.query.get_meta(), default_order=asc)) else: if col not in self.query.extra_select: order_by.append(( OrderBy(RawSQL(*self.query.extra[col]), descending=descending), False)) else: order_by.append(( OrderBy(Ref(col, RawSQL(*self.query.extra[col])), descending=descending), True)) result = [] seen = set() for expr, is_ref in order_by: resolved = expr.resolve_expression( self.query, allow_joins=True, reuse=None) sql, params = self.compile(resolved) without_ordering = self.ordering_parts.search(sql).group(1) if (without_ordering, tuple(params)) in seen: continue seen.add((without_ordering, tuple(params))) result.append((resolved, (sql, params, is_ref))) return result
def get_ordering(self): """ Returns a tuple containing a list representing the SQL elements in the "order by" clause, and the list of SQL elements that need to be added to the GROUP BY clause as a result of the ordering. Also sets the ordering_aliases attribute on this instance to a list of extra aliases needed in the select. Determining the ordering SQL can change the tables we need to include, so this should be run *before* get_from_clause(). """ if self.query.extra_order_by: ordering = self.query.extra_order_by elif not self.query.default_ordering: ordering = self.query.order_by else: ordering = self.query.order_by or self.query.model._meta.ordering qn = self.quote_name_unless_alias qn2 = self.connection.ops.quote_name distinct = self.query.distinct select_aliases = self._select_aliases result = [] group_by = [] ordering_aliases = [] if self.query.standard_ordering: asc, desc = ORDER_DIR['ASC'] else: asc, desc = ORDER_DIR['DESC'] # It's possible, due to model inheritance, that normal usage might try # to include the same field more than once in the ordering. We track # the table/column pairs we use and discard any after the first use. processed_pairs = set() for field in ordering: if field == '?': result.append(self.connection.ops.random_function_sql()) continue if isinstance(field, int): if field < 0: order = desc field = -field else: order = asc result.append('%s %s' % (field, order)) group_by.append((field, [])) continue col, order = get_order_dir(field, asc) if col in self.query.aggregate_select: result.append('%s %s' % (qn(col), order)) continue if '.' in field: # This came in through an extra(order_by=...) addition. Pass it # on verbatim. table, col = col.split('.', 1) if (table, col) not in processed_pairs: elt = '%s.%s' % (qn(table), col) processed_pairs.add((table, col)) if not distinct or elt in select_aliases: result.append('%s %s' % (elt, order)) group_by.append((elt, [])) elif get_order_dir(field)[0] not in self.query.extra_select: # 'col' is of the form 'field' or 'field1__field2' or # '-field1__field2__field', etc. for table, col, order in self.find_ordering_name( field, self.query.model._meta, default_order=asc): if (table, col) not in processed_pairs: elt = '%s.%s' % (qn(table), qn2(col)) processed_pairs.add((table, col)) if distinct and elt not in select_aliases: ordering_aliases.append(elt) result.append('%s %s' % (elt, order)) group_by.append((elt, [])) else: elt = qn2(col) if distinct and col not in select_aliases: ordering_aliases.append(elt) result.append('%s %s' % (elt, order)) group_by.append(self.query.extra_select[col]) self.query.ordering_aliases = ordering_aliases return result, group_by
def get_order_by(self): """ See original get_group_by at django.db.models.sql.compiler>SQLCompiler """ if self.query.extra_order_by: ordering = self.query.extra_order_by elif not self.query.default_ordering: ordering = self.query.order_by else: ordering = (self.query.order_by or self.query.get_meta().ordering or []) if self.query.standard_ordering: asc, desc = ORDER_DIR['ASC'] else: asc, desc = ORDER_DIR['DESC'] order_by = [] for field in ordering: if hasattr(field, 'resolve_expression'): if not isinstance(field, OrderBy): field = field.asc() if not self.query.standard_ordering: field.reverse_ordering() order_by.append((field, False)) continue if field == '?': # random order_by.append((OrderBy(Random()), False)) continue col, order = get_order_dir(field, asc) descending = True if order == 'DESC' else False if col in self.query.annotation_select: # Reference to expression in SELECT clause order_by.append((OrderBy(Ref(col, self.query.annotation_select[col]), descending=descending), True)) continue if col in self.query.annotations: # References to an expression which is masked out of the SELECT clause order_by.append((OrderBy(self.query.annotations[col], descending=descending), False)) continue if '.' in field: # This came in through an extra(order_by=...) addition. Pass it # on verbatim. table, col = col.split('.', 1) order_by.append((OrderBy(RawSQL( '%s.%s' % (self.quote_name_unless_alias(table), col), []), descending=descending), False)) continue if not self.query._extra or col not in self.query._extra: # 'col' is of the form 'field' or 'field1__field2' or # '-field1__field2__field', etc. order_by.extend( self.find_ordering_name(field, self.query.get_meta(), default_order=asc)) else: if col not in self.query.extra_select: order_by.append((OrderBy(RawSQL(*self.query.extra[col]), descending=descending), False)) else: order_by.append((OrderBy(Ref(col, RawSQL(*self.query.extra[col])), descending=descending), True)) result = [] # changed from set() to [] seen = [] for expr, is_ref in order_by: if self.query.combinator: src = expr.get_source_expressions()[0] # Relabel order by columns to raw numbers if this is a combined # query; necessary since the columns can't be referenced by the # fully qualified name and the simple column names may collide. for idx, (sel_expr, _, col_alias) in enumerate(self.select): if is_ref and col_alias == src.refs: src = src.source elif col_alias: continue if src == sel_expr: expr.set_source_expressions([RawSQL('%d' % (idx + 1), ())]) break else: raise DatabaseError( 'ORDER BY term does not match any column in the result set.' ) resolved = expr.resolve_expression(self.query, allow_joins=True, reuse=None) sql, params = self.compile(resolved) # Don't add the same column twice, but the order direction is # not taken into account so we strip it. When this entire method # is refactored into expressions, then we can check each part as we # generate it. without_ordering = self.ordering_parts.search(sql).group(1) if (without_ordering, tuple(params)) in seen: continue # changed from add to append seen.append((without_ordering, tuple(params))) result.append((resolved, (sql, params, is_ref))) return result
def get_ordering(self): """ Returns a tuple containing a list representing the SQL elements in the "order by" clause, and the list of SQL elements that need to be added to the GROUP BY clause as a result of the ordering. Also sets the ordering_aliases attribute on this instance to a list of extra aliases needed in the select. Determining the ordering SQL can change the tables we need to include, so this should be run *before* get_from_clause(). """ if self.query.extra_order_by: ordering = self.query.extra_order_by elif not self.query.default_ordering: ordering = self.query.order_by else: ordering = (self.query.order_by or self.query.get_meta().ordering or []) qn = self.quote_name_unless_alias qn2 = self.connection.ops.quote_name distinct = self.query.distinct select_aliases = self._select_aliases result = [] group_by = [] ordering_aliases = [] if self.query.standard_ordering: asc, desc = ORDER_DIR['ASC'] else: asc, desc = ORDER_DIR['DESC'] # It's possible, due to model inheritance, that normal usage might try # to include the same field more than once in the ordering. We track # the table/column pairs we use and discard any after the first use. processed_pairs = set() params = [] ordering_params = [] # For plain DISTINCT queries any ORDER BY clause must appear # in SELECT clause. # http://www.postgresql.org/message-id/[email protected] must_append_to_select = distinct and not self.query.distinct_fields for pos, field in enumerate(ordering): if field == '?': result.append(self.connection.ops.random_function_sql()) continue if isinstance(field, int): if field < 0: order = desc field = -field else: order = asc result.append('%s %s' % (field, order)) group_by.append((str(field), [])) continue col, order = get_order_dir(field, asc) if col in self.query.annotation_select: result.append('%s %s' % (qn(col), order)) continue if '.' in field: # This came in through an extra(order_by=...) addition. Pass it # on verbatim. table, col = col.split('.', 1) if (table, col) not in processed_pairs: elt = '%s.%s' % (qn(table), col) processed_pairs.add((table, col)) if not must_append_to_select or elt in select_aliases: result.append('%s %s' % (elt, order)) group_by.append((elt, [])) elif not self.query._extra or get_order_dir( field)[0] not in self.query._extra: # 'col' is of the form 'field' or 'field1__field2' or # '-field1__field2__field', etc. for table, cols, order in self.find_ordering_name( field, self.query.get_meta(), default_order=asc): for col in cols: if (table, col) not in processed_pairs: elt = '%s.%s' % (qn(table), qn2(col)) processed_pairs.add((table, col)) if must_append_to_select and elt not in select_aliases: ordering_aliases.append(elt) result.append('%s %s' % (elt, order)) group_by.append((elt, [])) else: elt = qn2(col) if col not in self.query.extra_select: if must_append_to_select: sql = "(%s) AS %s" % (self.query.extra[col][0], elt) ordering_aliases.append(sql) ordering_params.extend(self.query.extra[col][1]) result.append('%s %s' % (elt, order)) else: result.append("(%s) %s" % (self.query.extra[col][0], order)) params.extend(self.query.extra[col][1]) else: result.append('%s %s' % (elt, order)) group_by.append(self.query.extra[col]) self.ordering_aliases = ordering_aliases self.ordering_params = ordering_params return result, params, group_by
def get_order_by(self): """ Returns a list of 2-tuples of form (expr, (sql, params)) for the ORDER BY clause. The order_by clause can alter the select clause (for example it can add aliases to clauses that do not yet have one, or it can add totally new select clauses). """ if self.query.extra_order_by: ordering = self.query.extra_order_by elif not self.query.default_ordering: ordering = self.query.order_by else: ordering = (self.query.order_by or self.query.get_meta().ordering or []) if self.query.standard_ordering: asc, desc = ORDER_DIR['ASC'] else: asc, desc = ORDER_DIR['DESC'] order_by = [] for pos, field in enumerate(ordering): if hasattr(field, 'resolve_expression'): if not isinstance(field, OrderBy): field = field.asc() if not self.query.standard_ordering: field.reverse_ordering() order_by.append((field, False)) continue if field == '?': # random order_by.append((OrderBy(Random()), False)) continue col, order = get_order_dir(field, asc) descending = True if order == 'DESC' else False if col in self.query.annotation_select: order_by.append( (OrderBy(Ref(col, self.query.annotation_select[col]), descending=descending), True)) continue if '.' in field: # This came in through an extra(order_by=...) addition. Pass it # on verbatim. table, col = col.split('.', 1) order_by.append((OrderBy(RawSQL( '%s.%s' % (self.quote_name_unless_alias(table), col), []), descending=descending), False)) continue if not self.query._extra or col not in self.query._extra: # 'col' is of the form 'field' or 'field1__field2' or # '-field1__field2__field', etc. order_by.extend( self.find_ordering_name(field, self.query.get_meta(), default_order=asc)) else: if col not in self.query.extra_select: order_by.append((OrderBy(RawSQL(*self.query.extra[col]), descending=descending), False)) else: order_by.append( (OrderBy(Ref(col, RawSQL(*self.query.extra[col])), descending=descending), True)) result = [] seen = set() for expr, is_ref in order_by: resolved = expr.resolve_expression(self.query, allow_joins=True, reuse=None) sql, params = self.compile(resolved) # Don't add the same column twice, but the order direction is # not taken into account so we strip it. When this entire method # is refactored into expressions, then we can check each part as we # generate it. without_ordering = self.ordering_parts.search(sql).group(1) if (without_ordering, tuple(params)) in seen: continue seen.add((without_ordering, tuple(params))) result.append((resolved, (sql, params, is_ref))) return result
def get_order_by(self): """ Return a list of 2-tuples of form (expr, (sql, params, is_ref)) for the ORDER BY clause. The order_by clause can alter the select clause (for example it can add aliases to clauses that do not yet have one, or it can add totally new select clauses). """ if self.query.extra_order_by: ordering = self.query.extra_order_by elif not self.query.default_ordering: ordering = self.query.order_by elif self.query.order_by: ordering = self.query.order_by elif self.query.get_meta().ordering: ordering = self.query.get_meta().ordering self._meta_ordering = ordering else: ordering = [] if self.query.standard_ordering: asc, desc = ORDER_DIR['ASC'] else: asc, desc = ORDER_DIR['DESC'] order_by = [] for field in ordering: if hasattr(field, 'resolve_expression'): if isinstance(field, Value): # output_field must be resolved for constants. field = Cast(field, field.output_field) if not isinstance(field, OrderBy): field = field.asc() if not self.query.standard_ordering: field = field.copy() field.reverse_ordering() order_by.append((field, True)) else: order_by.append((field, False)) continue if field == '?': # random order_by.append((OrderBy(Random()), False)) continue col, order = get_order_dir(field, asc) descending = order == 'DESC' if col in self.query.annotation_select: # Reference to expression in SELECT clause order_by.append( (OrderBy(Ref(col, self.query.annotation_select[col]), descending=descending), True)) continue if col in self.query.annotations: # References to an expression which is masked out of the SELECT # clause. if self.query.combinator and self.select: # Don't use the resolved annotation because other # combined queries might define it differently. expr = F(col) else: expr = self.query.annotations[col] if isinstance(expr, Value): # output_field must be resolved for constants. expr = Cast(expr, expr.output_field) order_by.append((OrderBy(expr, descending=descending), False)) continue if '.' in field: # This came in through an extra(order_by=...) addition. Pass it # on verbatim. table, col = col.split('.', 1) order_by.append((OrderBy(RawSQL( '%s.%s' % (self.quote_name_unless_alias(table), col), []), descending=descending), False)) continue if not self.query.extra or col not in self.query.extra: if self.query.combinator and self.select: # Don't use the first model's field because other # combined queries might define it differently. order_by.append((OrderBy(F(col), descending=descending), False)) else: # 'col' is of the form 'field' or 'field1__field2' or # '-field1__field2__field', etc. order_by.extend( self.find_ordering_name(field, self.query.get_meta(), default_order=asc)) else: if col not in self.query.extra_select: order_by.append((OrderBy(RawSQL(*self.query.extra[col]), descending=descending), False)) else: order_by.append( (OrderBy(Ref(col, RawSQL(*self.query.extra[col])), descending=descending), True)) result = [] seen = set() for expr, is_ref in order_by: resolved = expr.resolve_expression(self.query, allow_joins=True, reuse=None) if self.query.combinator and self.select: src = resolved.get_source_expressions()[0] expr_src = expr.get_source_expressions()[0] # Relabel order by columns to raw numbers if this is a combined # query; necessary since the columns can't be referenced by the # fully qualified name and the simple column names may collide. for idx, (sel_expr, _, col_alias) in enumerate(self.select): if is_ref and col_alias == src.refs: src = src.source elif col_alias and not (isinstance(expr_src, F) and col_alias == expr_src.name): continue if src == sel_expr: resolved.set_source_expressions( [RawSQL('%d' % (idx + 1), ())]) break else: if col_alias: raise DatabaseError( 'ORDER BY term does not match any column in the result set.' ) # Add column used in ORDER BY clause without an alias to # the selected columns. order_by_idx = len(self.query.select) + 1 col_name = f'__orderbycol{order_by_idx}' for q in self.query.combined_queries: q.add_annotation(expr_src, col_name) self.query.add_select_col(src, col_name) resolved.set_source_expressions( [RawSQL(f'{order_by_idx}', ())]) sql, params = self.compile(resolved) # Don't add the same column twice, but the order direction is # not taken into account so we strip it. When this entire method # is refactored into expressions, then we can check each part as we # generate it. without_ordering = self.ordering_parts.search(sql)[1] params_hash = make_hashable(params) if (without_ordering, params_hash) in seen: continue seen.add((without_ordering, params_hash)) result.append((resolved, (sql, params, is_ref))) return result
def get_next_or_previous(item, qs, filters, next): if next: default_ordering = 'ASC' else: default_ordering = 'DESC' # First, determine the ordering. This code is from get_ordering() in # django.db.sql.compiler if qs.query.extra_order_by: ordering = qs.query.extra_order_by elif qs.query.default_ordering: ordering = qs.query.order_by else: ordering = qs.query.model._meta.ordering assert not ordering == '?', 'This makes no sense for random ordering.' # Ignore last field (pk) for field in ordering[:len(ordering)-1]: query_filter = None if field[0] == '-': item_value = getattr(item, field[1:]) else: item_value = getattr(item, field) if item_value is None: continue # Account for possible reverse ordering field, direction = get_order_dir(field, default_ordering) # Either make sure we filter increased values or lesser values # depending on the sort order if direction == 'ASC': filter_dict = {'%s__gt' % field: item_value} else: filter_dict = {'%s__lt' % field: item_value} # Make sure we nicely or the conditions for the queryset query_filter = Q(**filter_dict) print query_filter if query_filter is not None: qs = qs.filter(query_filter) print qs.count() # Reverse the order if we're looking for previous items l = list(qs) pos = 0 if default_ordering == 'DESC' and len(l) > 0: pos = len(l)-1 ref = l[pos] while pos > 0: alldiff = True for field in ordering: if field[0] == '-': field = field[1:] alldiff = alldiff and getattr(l[pos-1], field) != getattr(ref, field) if alldiff == True: break pos -= 1 # Return either the next/previous item or None if not existent try: info = (item._meta.app_label, item._meta.object_name.lower()) url = reverse('admin:%s_%s_change' % info, args=(l[pos].pk,)) return url+"?"+filters except IndexError: return None
def get_order_by(self): """ Returns a list of 2-tuples of form (expr, (sql, params)) for the ORDER BY clause. The order_by clause can alter the select clause (for example it can add aliases to clauses that do not yet have one, or it can add totally new select clauses). """ if self.query.extra_order_by: ordering = self.query.extra_order_by elif not self.query.default_ordering: ordering = self.query.order_by else: ordering = (self.query.order_by or self.query.get_meta().ordering or []) if self.query.standard_ordering: asc, desc = ORDER_DIR['ASC'] else: asc, desc = ORDER_DIR['DESC'] order_by = [] for pos, field in enumerate(ordering): if hasattr(field, 'resolve_expression'): if not isinstance(field, OrderBy): field = field.asc() if not self.query.standard_ordering: field.reverse_ordering() order_by.append((field, False)) continue if field == '?': # random order_by.append((OrderBy(Random()), False)) continue col, order = get_order_dir(field, asc) descending = True if order == 'DESC' else False if col in self.query.annotation_select: # Reference to expression in SELECT clause order_by.append(( OrderBy(Ref(col, self.query.annotation_select[col]), descending=descending), True)) continue if col in self.query.annotations: # References to an expression which is masked out of the SELECT clause order_by.append(( OrderBy(self.query.annotations[col], descending=descending), False)) continue if '.' in field: # This came in through an extra(order_by=...) addition. Pass it # on verbatim. table, col = col.split('.', 1) order_by.append(( OrderBy( RawSQL('%s.%s' % (self.quote_name_unless_alias(table), col), []), descending=descending ), False)) continue if not self.query._extra or col not in self.query._extra: # 'col' is of the form 'field' or 'field1__field2' or # '-field1__field2__field', etc. order_by.extend(self.find_ordering_name( field, self.query.get_meta(), default_order=asc)) else: if col not in self.query.extra_select: order_by.append(( OrderBy(RawSQL(*self.query.extra[col]), descending=descending), False)) else: order_by.append(( OrderBy(Ref(col, RawSQL(*self.query.extra[col])), descending=descending), True)) result = [] seen = set() for expr, is_ref in order_by: resolved = expr.resolve_expression( self.query, allow_joins=True, reuse=None) sql, params = self.compile(resolved) # Don't add the same column twice, but the order direction is # not taken into account so we strip it. When this entire method # is refactored into expressions, then we can check each part as we # generate it. without_ordering = self.ordering_parts.search(sql).group(1) if (without_ordering, tuple(params)) in seen: continue seen.add((without_ordering, tuple(params))) result.append((resolved, (sql, params, is_ref))) return result
def get_ordering(self): """ Returns a tuple containing a list representing the SQL elements in the "order by" clause, and the list of SQL elements that need to be added to the GROUP BY clause as a result of the ordering. Also sets the ordering_aliases attribute on this instance to a list of extra aliases needed in the select. Determining the ordering SQL can change the tables we need to include, so this should be run *before* get_from_clause(). """ if self.query.extra_order_by: ordering = self.query.extra_order_by elif not self.query.default_ordering: ordering = self.query.order_by else: ordering = (self.query.order_by or self.query.get_meta().ordering or []) qn = self.quote_name_unless_alias qn2 = self.connection.ops.quote_name distinct = self.query.distinct select_aliases = self._select_aliases result = [] group_by = [] ordering_aliases = [] if self.query.standard_ordering: asc, desc = ORDER_DIR['ASC'] else: asc, desc = ORDER_DIR['DESC'] # It's possible, due to model inheritance, that normal usage might try # to include the same field more than once in the ordering. We track # the table/column pairs we use and discard any after the first use. processed_pairs = set() params = [] ordering_params = [] # For plain DISTINCT queries any ORDER BY clause must appear # in SELECT clause. # http://www.postgresql.org/message-id/[email protected] must_append_to_select = distinct and not self.query.distinct_fields for pos, field in enumerate(ordering): if field == '?': result.append(self.connection.ops.random_function_sql()) continue if isinstance(field, int): if field < 0: order = desc field = -field else: order = asc result.append('%s %s' % (field, order)) group_by.append((str(field), [])) continue col, order = get_order_dir(field, asc) if col in self.query.aggregate_select: result.append('%s %s' % (qn(col), order)) continue if '.' in field: # This came in through an extra(order_by=...) addition. Pass it # on verbatim. table, col = col.split('.', 1) if (table, col) not in processed_pairs: elt = '%s.%s' % (qn(table), col) processed_pairs.add((table, col)) if not must_append_to_select or elt in select_aliases: result.append('%s %s' % (elt, order)) group_by.append((elt, [])) elif not self.query._extra or get_order_dir(field)[0] not in self.query._extra: # 'col' is of the form 'field' or 'field1__field2' or # '-field1__field2__field', etc. for table, cols, order in self.find_ordering_name(field, self.query.get_meta(), default_order=asc): for col in cols: if (table, col) not in processed_pairs: elt = '%s.%s' % (qn(table), qn2(col)) processed_pairs.add((table, col)) if must_append_to_select and elt not in select_aliases: ordering_aliases.append(elt) result.append('%s %s' % (elt, order)) group_by.append((elt, [])) else: elt = qn2(col) if col not in self.query.extra_select: if must_append_to_select: sql = "(%s) AS %s" % (self.query.extra[col][0], elt) ordering_aliases.append(sql) ordering_params.extend(self.query.extra[col][1]) result.append('%s %s' % (elt, order)) else: result.append("(%s) %s" % (self.query.extra[col][0], order)) params.extend(self.query.extra[col][1]) else: result.append('%s %s' % (elt, order)) group_by.append(self.query.extra[col]) self.ordering_aliases = ordering_aliases self.ordering_params = ordering_params return result, params, group_by