7
7
all about the internals of models in order to get the information it needs.
10
from copy import deepcopy
10
from django.utils.copycompat import deepcopy
12
11
from django.utils.tree import Node
13
12
from django.utils.datastructures import SortedDict
14
13
from django.utils.encoding import force_unicode
15
from django.db.backends.util import truncate_name
16
from django.db import connection
14
from django.db import connections, DEFAULT_DB_ALIAS
17
15
from django.db.models import signals
18
16
from django.db.models.fields import FieldDoesNotExist
19
from django.db.models.query_utils import select_related_descend
17
from django.db.models.query_utils import select_related_descend, InvalidQuery
20
18
from django.db.models.sql import aggregates as base_aggregates_module
19
from django.db.models.sql.constants import *
20
from django.db.models.sql.datastructures import EmptyResultSet, Empty, MultiJoin
21
21
from django.db.models.sql.expressions import SQLEvaluator
22
from django.db.models.sql.where import WhereNode, Constraint, EverythingNode, AND, OR
22
from django.db.models.sql.where import (WhereNode, Constraint, EverythingNode,
23
24
from django.core.exceptions import FieldError
24
from datastructures import EmptyResultSet, Empty, MultiJoin
25
from constants import *
30
from sets import Set as set # Python 2.3 fallback
32
__all__ = ['Query', 'BaseQuery']
34
class BaseQuery(object):
26
__all__ = ['Query', 'RawQuery']
28
class RawQuery(object):
30
A single raw SQL query
33
def __init__(self, sql, using, params=None):
34
self.validate_sql(sql)
35
self.params = params or ()
40
# Mirror some properties of a normal query so that
41
# the compiler can be used to process results.
42
self.low_mark, self.high_mark = 0, None # Used for offset/limit
43
self.extra_select = {}
44
self.aggregate_select = {}
46
def clone(self, using):
47
return RawQuery(self.sql, using, params=self.params)
49
def convert_values(self, value, field, connection):
50
"""Convert the database-returned value into a type that is consistent
51
across database backends.
53
By default, this defers to the underlying backend operations, but
54
it can be overridden by Query classes for specific backends.
56
return connection.ops.convert_values(value, field)
58
def get_columns(self):
59
if self.cursor is None:
61
converter = connections[self.using].introspection.table_name_converter
62
return [converter(column_meta[0])
63
for column_meta in self.cursor.description]
65
def validate_sql(self, sql):
66
if not sql.lower().strip().startswith('select'):
67
raise InvalidQuery('Raw queries are limited to SELECT queries. Use '
68
'connection.cursor directly for other types of queries.')
71
# Always execute a new query for a new iterator.
72
# This could be optimized with a cache at the expense of RAM.
74
if not connections[self.using].features.can_use_chunked_reads:
75
# If the database can't use chunked reads we need to make sure we
76
# evaluate the entire query up front.
77
result = list(self.cursor)
83
return "<RawQuery: %r>" % (self.sql % self.params)
85
def _execute_query(self):
86
self.cursor = connections[self.using].cursor()
87
self.cursor.execute(self.sql, self.params)
36
92
A single SQL query.
387
def as_sql(self, with_limits=True, with_col_aliases=False):
389
Creates the SQL for this query. Returns the SQL string and list of
392
If 'with_limits' is False, any limit/offset information is not included
396
out_cols = self.get_columns(with_col_aliases)
397
ordering, ordering_group_by = self.get_ordering()
399
# This must come after 'select' and 'ordering' -- see docstring of
400
# get_from_clause() for details.
401
from_, f_params = self.get_from_clause()
403
qn = self.quote_name_unless_alias
404
where, w_params = self.where.as_sql(qn=qn)
405
having, h_params = self.having.as_sql(qn=qn)
407
for val in self.extra_select.itervalues():
408
params.extend(val[1])
412
result.append('DISTINCT')
413
result.append(', '.join(out_cols + self.ordering_aliases))
415
result.append('FROM')
417
params.extend(f_params)
420
result.append('WHERE %s' % where)
421
params.extend(w_params)
424
result.append('WHERE')
427
result.append(' AND '.join(self.extra_where))
429
grouping, gb_params = self.get_grouping()
432
# If the backend can't group by PK (i.e., any database
433
# other than MySQL), then any fields mentioned in the
434
# ordering clause needs to be in the group by clause.
435
if not self.connection.features.allows_group_by_pk:
436
for col, col_params in ordering_group_by:
437
if col not in grouping:
438
grouping.append(str(col))
439
gb_params.extend(col_params)
441
ordering = self.connection.ops.force_no_ordering()
442
result.append('GROUP BY %s' % ', '.join(grouping))
443
params.extend(gb_params)
446
result.append('HAVING %s' % having)
447
params.extend(h_params)
450
result.append('ORDER BY %s' % ', '.join(ordering))
453
if self.high_mark is not None:
454
result.append('LIMIT %d' % (self.high_mark - self.low_mark))
456
if self.high_mark is None:
457
val = self.connection.ops.no_limit_value()
459
result.append('LIMIT %d' % val)
460
result.append('OFFSET %d' % self.low_mark)
462
params.extend(self.extra_params)
463
return ' '.join(result), tuple(params)
465
def as_nested_sql(self):
467
Perform the same functionality as the as_sql() method, returning an
468
SQL string and parameters. However, the alias prefixes are bumped
469
beforehand (in a copy -- the current query isn't changed) and any
472
Used when nesting this query inside another.
475
obj.clear_ordering(True)
405
def has_results(self, using):
407
q.add_extra({'a': 1}, None, None, None, None, None)
410
q.default_cols = False
411
q.select_related = False
412
q.set_extra_mask(('a',))
413
q.set_aggregate_mask(())
414
q.clear_ordering(True)
416
compiler = q.get_compiler(using=using)
417
return bool(compiler.execute_sql(SINGLE))
479
419
def combine(self, rhs, connector):
687
599
for field in fields:
688
600
target[table].add(field.column)
690
def get_columns(self, with_aliases=False):
692
Returns the list of columns to use in the select statement. If no
693
columns have been specified, returns all columns relating to fields in
696
If 'with_aliases' is true, any column names that are duplicated
697
(without the table names) are given unique aliases. This is needed in
698
some cases to avoid ambiguity with nested queries.
700
qn = self.quote_name_unless_alias
701
qn2 = self.connection.ops.quote_name
702
result = ['(%s) AS %s' % (col[0], qn2(alias)) for alias, col in self.extra_select.iteritems()]
703
aliases = set(self.extra_select.keys())
705
col_aliases = aliases.copy()
709
only_load = self.deferred_to_columns()
710
for col in self.select:
711
if isinstance(col, (list, tuple)):
713
table = self.alias_map[alias][TABLE_NAME]
714
if table in only_load and col not in only_load[table]:
716
r = '%s.%s' % (qn(alias), qn(column))
718
if col[1] in col_aliases:
719
c_alias = 'Col%d' % len(col_aliases)
720
result.append('%s AS %s' % (r, c_alias))
722
col_aliases.add(c_alias)
724
result.append('%s AS %s' % (r, qn2(col[1])))
726
col_aliases.add(col[1])
730
col_aliases.add(col[1])
732
result.append(col.as_sql(quote_func=qn))
734
if hasattr(col, 'alias'):
735
aliases.add(col.alias)
736
col_aliases.add(col.alias)
738
elif self.default_cols:
739
cols, new_aliases = self.get_default_columns(with_aliases,
742
aliases.update(new_aliases)
746
aggregate.as_sql(quote_func=qn),
747
alias is not None and ' AS %s' % qn(alias) or ''
749
for alias, aggregate in self.aggregate_select.items()
752
for table, col in self.related_select_cols:
753
r = '%s.%s' % (qn(table), qn(col))
754
if with_aliases and col in col_aliases:
755
c_alias = 'Col%d' % len(col_aliases)
756
result.append('%s AS %s' % (r, c_alias))
758
col_aliases.add(c_alias)
764
self._select_aliases = aliases
767
def get_default_columns(self, with_aliases=False, col_aliases=None,
768
start_alias=None, opts=None, as_pairs=False):
770
Computes the default columns for selecting every field in the base
771
model. Will sometimes be called to pull in related models (e.g. via
772
select_related), in which case "opts" and "start_alias" will be given
773
to provide a starting point for the traversal.
775
Returns a list of strings, quoted appropriately for use in SQL
776
directly, as well as a set of aliases used in the select statement (if
777
'as_pairs' is True, returns a list of (alias, col_name) pairs instead
778
of strings as the first component and None as the second component).
782
opts = self.model._meta
783
qn = self.quote_name_unless_alias
784
qn2 = self.connection.ops.quote_name
786
only_load = self.deferred_to_columns()
787
# Skip all proxy to the root proxied model
788
proxied_model = get_proxied_model(opts)
791
seen = {None: start_alias}
792
for field, model in opts.get_fields_with_model():
797
if model is proxied_model:
800
link_field = opts.get_ancestor_link(model)
801
alias = self.join((start_alias, model._meta.db_table,
802
link_field.column, model._meta.pk.column))
805
# If we're starting from the base model of the queryset, the
806
# aliases will have already been set up in pre_sql_setup(), so
807
# we can save time here.
808
alias = self.included_inherited_models[model]
809
table = self.alias_map[alias][TABLE_NAME]
810
if table in only_load and field.column not in only_load[table]:
813
result.append((alias, field.column))
816
if with_aliases and field.column in col_aliases:
817
c_alias = 'Col%d' % len(col_aliases)
818
result.append('%s.%s AS %s' % (qn(alias),
819
qn2(field.column), c_alias))
820
col_aliases.add(c_alias)
823
r = '%s.%s' % (qn(alias), qn2(field.column))
827
col_aliases.add(field.column)
828
return result, aliases
830
def get_from_clause(self):
832
Returns a list of strings that are joined together to go after the
833
"FROM" part of the query, as well as a list any extra parameters that
834
need to be included. Sub-classes, can override this to create a
835
from-clause via a "select".
837
This should only be called after any SQL construction methods that
838
might change the tables we need. This means the select columns and
839
ordering must be done first.
842
qn = self.quote_name_unless_alias
843
qn2 = self.connection.ops.quote_name
845
for alias in self.tables:
846
if not self.alias_refcount[alias]:
849
name, alias, join_type, lhs, lhs_col, col, nullable = self.alias_map[alias]
851
# Extra tables can end up in self.tables, but not in the
852
# alias_map if they aren't in a join. That's OK. We skip them.
854
alias_str = (alias != name and ' %s' % alias or '')
855
if join_type and not first:
856
result.append('%s %s%s ON (%s.%s = %s.%s)'
857
% (join_type, qn(name), alias_str, qn(lhs),
858
qn2(lhs_col), qn(alias), qn2(col)))
860
connector = not first and ', ' or ''
861
result.append('%s%s%s' % (connector, qn(name), alias_str))
863
for t in self.extra_tables:
864
alias, unused = self.table_alias(t)
865
# Only add the alias if it's not already present (the table_alias()
866
# calls increments the refcount, so an alias refcount of one means
867
# this is the only reference.
868
if alias not in self.alias_map or self.alias_refcount[alias] == 1:
869
connector = not first and ', ' or ''
870
result.append('%s%s' % (connector, qn(alias)))
874
def get_grouping(self):
876
Returns a tuple representing the SQL elements in the "group by" clause.
878
qn = self.quote_name_unless_alias
879
result, params = [], []
880
if self.group_by is not None:
881
group_by = self.group_by or []
884
for extra_select, extra_params in self.extra_select.itervalues():
885
extra_selects.append(extra_select)
886
params.extend(extra_params)
887
for col in group_by + self.related_select_cols + extra_selects:
888
if isinstance(col, (list, tuple)):
889
result.append('%s.%s' % (qn(col[0]), qn(col[1])))
890
elif hasattr(col, 'as_sql'):
891
result.append(col.as_sql(qn))
893
result.append(str(col))
894
return result, params
896
def get_ordering(self):
898
Returns a tuple containing a list representing the SQL elements in the
899
"order by" clause, and the list of SQL elements that need to be added
900
to the GROUP BY clause as a result of the ordering.
902
Also sets the ordering_aliases attribute on this instance to a list of
903
extra aliases needed in the select.
905
Determining the ordering SQL can change the tables we need to include,
906
so this should be run *before* get_from_clause().
908
if self.extra_order_by:
909
ordering = self.extra_order_by
910
elif not self.default_ordering:
911
ordering = self.order_by
913
ordering = self.order_by or self.model._meta.ordering
914
qn = self.quote_name_unless_alias
915
qn2 = self.connection.ops.quote_name
916
distinct = self.distinct
917
select_aliases = self._select_aliases
920
ordering_aliases = []
921
if self.standard_ordering:
922
asc, desc = ORDER_DIR['ASC']
924
asc, desc = ORDER_DIR['DESC']
926
# It's possible, due to model inheritance, that normal usage might try
927
# to include the same field more than once in the ordering. We track
928
# the table/column pairs we use and discard any after the first use.
929
processed_pairs = set()
931
for field in ordering:
933
result.append(self.connection.ops.random_function_sql())
935
if isinstance(field, int):
941
result.append('%s %s' % (field, order))
942
group_by.append((field, []))
944
col, order = get_order_dir(field, asc)
945
if col in self.aggregate_select:
946
result.append('%s %s' % (col, order))
949
# This came in through an extra(order_by=...) addition. Pass it
951
table, col = col.split('.', 1)
952
if (table, col) not in processed_pairs:
953
elt = '%s.%s' % (qn(table), col)
954
processed_pairs.add((table, col))
955
if not distinct or elt in select_aliases:
956
result.append('%s %s' % (elt, order))
957
group_by.append((elt, []))
958
elif get_order_dir(field)[0] not in self.extra_select:
959
# 'col' is of the form 'field' or 'field1__field2' or
960
# '-field1__field2__field', etc.
961
for table, col, order in self.find_ordering_name(field,
962
self.model._meta, default_order=asc):
963
if (table, col) not in processed_pairs:
964
elt = '%s.%s' % (qn(table), qn2(col))
965
processed_pairs.add((table, col))
966
if distinct and elt not in select_aliases:
967
ordering_aliases.append(elt)
968
result.append('%s %s' % (elt, order))
969
group_by.append((elt, []))
972
if distinct and col not in select_aliases:
973
ordering_aliases.append(elt)
974
result.append('%s %s' % (elt, order))
975
group_by.append(self.extra_select[col])
976
self.ordering_aliases = ordering_aliases
977
return result, group_by
979
def find_ordering_name(self, name, opts, alias=None, default_order='ASC',
982
Returns the table alias (the name might be ambiguous, the alias will
983
not be) and column name for ordering by the given 'name' parameter.
984
The 'name' is of the form 'field1__field2__...__fieldN'.
986
name, order = get_order_dir(name, default_order)
987
pieces = name.split(LOOKUP_SEP)
989
alias = self.get_initial_alias()
990
field, target, opts, joins, last, extra = self.setup_joins(pieces,
995
# To avoid inadvertent trimming of a necessary alias, use the
996
# refcount to show that we are referencing a non-relation field on
998
self.ref_alias(alias)
1000
# Must use left outer joins for nullable fields and their relations.
1001
self.promote_alias_chain(joins,
1002
self.alias_map[joins[0]][JOIN_TYPE] == self.LOUTER)
1004
# If we get to this point and the field is a relation to another model,
1005
# append the default ordering for that model.
1006
if field.rel and len(joins) > 1 and opts.ordering:
1007
# Firstly, avoid infinite loops.
1008
if not already_seen:
1009
already_seen = set()
1010
join_tuple = tuple([self.alias_map[j][TABLE_NAME] for j in joins])
1011
if join_tuple in already_seen:
1012
raise FieldError('Infinite loop caused by ordering.')
1013
already_seen.add(join_tuple)
1016
for item in opts.ordering:
1017
results.extend(self.find_ordering_name(item, opts, alias,
1018
order, already_seen))
1022
# We have to do the same "final join" optimisation as in
1023
# add_filter, since the final column might not otherwise be part of
1024
# the select set (so we can't order on it).
1026
join = self.alias_map[alias]
1027
if col != join[RHS_JOIN_COL]:
1029
self.unref_alias(alias)
1030
alias = join[LHS_ALIAS]
1031
col = join[LHS_JOIN_COL]
1032
return [(alias, col, order)]
1034
603
def table_alias(self, table_name, create=False):
1333
902
self.unref_alias(alias)
1334
903
self.included_inherited_models = {}
1336
def fill_related_selections(self, opts=None, root_alias=None, cur_depth=1,
1337
used=None, requested=None, restricted=None, nullable=None,
1338
dupe_set=None, avoid_set=None):
1340
Fill in the information needed for a select_related query. The current
1341
depth is measured as the number of connections away from the root model
1342
(for example, cur_depth=1 means we are looking at models with direct
1343
connections to the root model).
1345
if not restricted and self.max_depth and cur_depth > self.max_depth:
1346
# We've recursed far enough; bail out.
1350
opts = self.get_meta()
1351
root_alias = self.get_initial_alias()
1352
self.related_select_cols = []
1353
self.related_select_fields = []
1356
if dupe_set is None:
1358
if avoid_set is None:
1360
orig_dupe_set = dupe_set
1362
# Setup for the case when only particular related fields should be
1363
# included in the related selection.
1364
if requested is None and restricted is not False:
1365
if isinstance(self.select_related, dict):
1366
requested = self.select_related
1371
for f, model in opts.get_fields_with_model():
1372
if not select_related_descend(f, restricted, requested):
1374
# The "avoid" set is aliases we want to avoid just for this
1375
# particular branch of the recursion. They aren't permanently
1376
# forbidden from reuse in the related selection tables (which is
1377
# what "used" specifies).
1378
avoid = avoid_set.copy()
1379
dupe_set = orig_dupe_set.copy()
1380
table = f.rel.to._meta.db_table
1381
if nullable or f.null:
1389
for int_model in opts.get_base_chain(model):
1390
# Proxy model have elements in base chain
1391
# with no parents, assign the new options
1392
# object and skip to the next base in that
1394
if not int_opts.parents[int_model]:
1395
int_opts = int_model._meta
1397
lhs_col = int_opts.parents[int_model].column
1398
dedupe = lhs_col in opts.duplicate_targets
1400
avoid.update(self.dupe_avoidance.get(id(opts), lhs_col),
1402
dupe_set.add((opts, lhs_col))
1403
int_opts = int_model._meta
1404
alias = self.join((alias, int_opts.db_table, lhs_col,
1405
int_opts.pk.column), exclusions=used,
1407
alias_chain.append(alias)
1408
for (dupe_opts, dupe_col) in dupe_set:
1409
self.update_dupe_avoidance(dupe_opts, dupe_col, alias)
1410
if self.alias_map[root_alias][JOIN_TYPE] == self.LOUTER:
1411
self.promote_alias_chain(alias_chain, True)
1415
dedupe = f.column in opts.duplicate_targets
1416
if dupe_set or dedupe:
1417
avoid.update(self.dupe_avoidance.get((id(opts), f.column), ()))
1419
dupe_set.add((opts, f.column))
1421
alias = self.join((alias, table, f.column,
1422
f.rel.get_related_field().column),
1423
exclusions=used.union(avoid), promote=promote)
1425
columns, aliases = self.get_default_columns(start_alias=alias,
1426
opts=f.rel.to._meta, as_pairs=True)
1427
self.related_select_cols.extend(columns)
1428
if self.alias_map[alias][JOIN_TYPE] == self.LOUTER:
1429
self.promote_alias_chain(aliases, True)
1430
self.related_select_fields.extend(f.rel.to._meta.fields)
1432
next = requested.get(f.name, {})
1435
if f.null is not None:
1436
new_nullable = f.null
1439
for dupe_opts, dupe_col in dupe_set:
1440
self.update_dupe_avoidance(dupe_opts, dupe_col, alias)
1441
self.fill_related_selections(f.rel.to._meta, alias, cur_depth + 1,
1442
used, next, restricted, new_nullable, dupe_set, avoid)
1444
906
def add_aggregate(self, aggregate, model, alias, is_summary):