def as_sql(self, with_limits=True, with_col_aliases=False, subquery=False):
""" Patching final SQL query."""
query = self.query
try:
where_sql, where_params = query.where.as_sql(self, self.connection)
except EmptyResultSet:
# Where node compiled to always-false condition, but we still need
# to call pre_sql_setup() and other methods by super().as_sql
pass
else:
# creating WHERE node with sphinx-aware node implementation if
# everything is fine
where = sqls.SphinxWhereNode()
# Adding MATCH() node to WHERE node if needed
self._add_match_extra(query, where)
query.where = where
# Adding where conditions to SELECT clause because of better
# support of SQL expressions in sphinxsearch.
self._add_where_result(query, where_sql, where_params)
sql, args = super().as_sql(with_limits, with_col_aliases)
# empty SQL doesn't need patching
if (sql, args) == ('', ()):
return sql, args
# removing unsupported by sphinxsearch OFFSET clause
# replacing it with LIMIT <offset>, <limit>
sql = re.sub(r'LIMIT (\d+) OFFSET (\d+)$', 'LIMIT \\2, \\1', sql)
# patching GROUP BY clause
group_limit = getattr(query, 'group_limit', '')
group_by_ordering = self.get_group_ordering()
if group_limit:
# add GROUP <N> BY expression
group_by = 'GROUP %s BY \\1' % group_limit
else:
group_by = 'GROUP BY \\1'
if group_by_ordering:
# add WITHIN GROUP ORDER BY expression
group_by += group_by_ordering
sql = re.sub(r'GROUP BY ((\w+)(, \w+)*)', group_by, sql)
# adding sphinxsearch OPTION clause
options = getattr(query, 'options', None)
if options:
keys = sorted(options.keys())
values = [options[k] for k in keys if k not in self.safe_options]
opts = []
for k in keys:
if k in self.safe_options:
opts.append("%s=%s" % (k, options[k]))
else:
opts.append("%s=%%s" % k)
sql += ' OPTION %s' % ', '.join(opts) or ''
args += tuple(values)
return sql, args
def as_sql(self, with_limits=True, with_col_aliases=False, subquery=False):
""" Patching final SQL query."""
where, self.query.where = self.query.where, sqls.SphinxWhereNode()
match = getattr(self.query, 'match', None)
if match:
# add match extra where
self._add_match_extra(match)
connection = self.connection
where_sql, where_params = where.as_sql(self, connection)
# moving where conditions to SELECT clause because of better support
# of SQL expressions in sphinxsearch.
if where_sql:
# Without annotation queryset.count() receives 1 as where_result
# and count it as aggregation result.
self.query.add_annotation(
sqls.SphinxWhereExpression(where_sql, where_params),
'__where_result')
# almost all where conditions are now in SELECT clause, so
# WHERE should contain only test against that conditions are true
self.query.add_extra(None, None, ['__where_result = %s'], (True, ),
None, None)
sql, args = super().as_sql(with_limits, with_col_aliases)
# empty SQL doesn't need patching
if (sql, args) == ('', ()):
return sql, args
# removing unsupported by sphinxsearch OFFSET clause
# replacing it with LIMIT <offset>, <limit>
sql = re.sub(r'LIMIT (\d+) OFFSET (\d+)$', 'LIMIT \\2, \\1', sql)
# patching GROUP BY clause
group_limit = getattr(self.query, 'group_limit', '')
group_by_ordering = self.get_group_ordering()
if group_limit:
# add GROUP <N> BY expression
group_by = 'GROUP %s BY \\1' % group_limit
else:
group_by = 'GROUP BY \\1'
if group_by_ordering:
# add WITHIN GROUP ORDER BY expression
group_by += group_by_ordering
sql = re.sub(r'GROUP BY ((\w+)(, \w+)*)', group_by, sql)
# adding sphinxsearch OPTION clause
options = getattr(self.query, 'options', None)
if options:
keys = sorted(options.keys())
values = [options[k] for k in keys if k not in self.safe_options]
opts = []
for k in keys:
if k in self.safe_options:
opts.append("%s=%s" % (k, options[k]))
else:
opts.append("%s=%%s" % k)
sql += ' OPTION %s' % ', '.join(opts) or ''
args += tuple(values)
return sql, args