def test_order_by_sql_query_with_order_by_name(self):
qs1 = Report.objects.values("name")
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, _ = compiler.as_sql()
self.assertEqual(
sql_compiled,
"SELECT tests_report.name FROM tests_report ORDER BY " +
"tests_report.name ASC",
)
def test_order_by_sql_query_with_order_by_null_first(self):
qs1 = Report.objects.values("name").order_by(
F("name").desc(nulls_first=True))
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, _ = compiler.as_sql()
self.assertEqual(
sql_compiled,
"SELECT tests_report.name FROM tests_report ORDER BY " +
"tests_report.name IS NOT NULL, tests_report.name DESC",
)
def test_startswith_endswith_sql_query_case_insensitive(self):
qs1 = Author.objects.filter(name__istartswith="abc").values("num")
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, params = compiler.as_sql()
self.assertEqual(
sql_compiled,
"SELECT tests_author.num FROM tests_author WHERE "
+ "REGEXP_CONTAINS(CAST(tests_author.name AS STRING), %s)",
)
self.assertEqual(params, ("(?i)^abc",))
def test_cast_param_to_float_with_no_params_query(self):
qs1 = Number.objects.filter(item_id__exact=F("num")).values("num")
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, params = compiler.as_sql()
self.assertEqual(
sql_compiled,
"SELECT tests_number.num FROM tests_number WHERE "
+ "tests_number.item_id = (tests_number.num)",
)
self.assertEqual(params, ())
def test_cast_param_to_float_for_foreign_key_field_query(self):
qs1 = Number.objects.filter(item_id__exact="10").values("num")
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, params = compiler.as_sql()
self.assertEqual(
sql_compiled,
"SELECT tests_number.num FROM tests_number WHERE "
+ "tests_number.item_id = %s",
)
self.assertEqual(params, (10,))
def test_cast_param_to_float_for_int_field_query(self):
qs1 = Number.objects.filter(num__lte=1.1).values("num")
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, params = compiler.as_sql()
self.assertEqual(
sql_compiled,
"SELECT tests_number.num FROM tests_number WHERE "
+ "tests_number.num <= %s",
)
self.assertEqual(params, (1,))
def test_cast_param_to_float_lte_sql_query(self):
qs1 = Number.objects.filter(
decimal_num__lte=Decimal("1.1")).values("decimal_num")
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, params = compiler.as_sql()
self.assertEqual(
sql_compiled,
"SELECT tests_number.decimal_num FROM tests_number WHERE " +
"tests_number.decimal_num <= %s",
)
self.assertEqual(params, (Decimal("1.1"), ))
def test_iexact_sql_query_case_insensitive_value_match(self):
qs1 = Author.objects.filter(name__upper__iexact="abc").values("name")
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, params = compiler.as_sql()
self.assertEqual(
sql_compiled,
"SELECT tests_author.name FROM tests_author WHERE "
+ "REGEXP_CONTAINS((UPPER(CONCAT('^(?i)', "
+ "CAST(UPPER(tests_author.name) AS STRING), '$'))), %s)",
)
self.assertEqual(params, ("abc",))
def test_contains_sql_query_case_sensitive_transform(self):
qs1 = Author.objects.filter(name__upper__contains="abc").values("name")
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, params = compiler.as_sql()
self.assertEqual(
sql_compiled,
"SELECT tests_author.name FROM tests_author WHERE "
+ "REGEXP_CONTAINS(CAST(UPPER(tests_author.name) AS STRING), "
+ 'REPLACE(REPLACE(REPLACE((UPPER(%s)), "\\\\", "\\\\\\\\"), '
+ '"%%", r"\\%%"), "_", r"\\_"))',
)
self.assertEqual(params, ("abc",))
def test_regex_sql_query_case_insensitive_with_transform(self):
qs1 = Author.objects.filter(name__upper__iregex="abc").values("num")
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, params = compiler.as_sql()
self.assertEqual(
sql_compiled,
"SELECT tests_author.num FROM tests_author WHERE "
+ "REGEXP_CONTAINS(CAST(UPPER(tests_author.name) AS STRING), "
+ "CONCAT('(?i)', (UPPER(%s))))",
)
self.assertEqual(params, ("abc",))
def test_startswith_endswith_sql_query_with_bileteral_transform(self):
qs1 = Author.objects.filter(name__upper__startswith="abc").values(
"name"
)
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, params = compiler.as_sql()
self.assertEqual(
sql_compiled,
"SELECT tests_author.name FROM tests_author WHERE "
+ "REGEXP_CONTAINS(CAST(UPPER(tests_author.name) AS STRING), "
+ "REPLACE(REPLACE(REPLACE(CONCAT('^', (UPPER(%s))), "
+ '"\\\\", "\\\\\\\\"), "%%", r"\\%%"), "_", r"\\_"))',
)
self.assertEqual(params, ("abc",))
def test_regex_sql_query_case_sensitive_with_transform(self):
qs1 = Author.objects.filter(name__upper__regex="abc").values("num")
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, params = compiler.as_sql()
if USING_DJANGO_3:
expected_sql = (
"SELECT tests_author.num FROM tests_author WHERE " +
"REGEXP_CONTAINS(CAST(UPPER(tests_author.name) AS STRING), " +
"UPPER(%s))")
else:
expected_sql = (
"SELECT tests_author.num FROM tests_author WHERE " +
"REGEXP_CONTAINS(CAST(UPPER(tests_author.name) AS STRING), " +
"(UPPER(%s)))")
self.assertEqual(sql_compiled, expected_sql)
self.assertEqual(params, ("abc", ))
def test_contains_sql_query_case_insensitive_transform(self):
qs1 = Author.objects.filter(
name__upper__icontains="abc").values("name")
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, params = compiler.as_sql()
if USING_DJANGO_3:
expected_sql = (
"SELECT tests_author.name FROM tests_author WHERE " +
"REGEXP_CONTAINS(CAST(UPPER(tests_author.name) AS STRING), " +
"REPLACE(REPLACE(REPLACE(CONCAT('(?i)', UPPER(%s)), " +
'"\\\\", "\\\\\\\\"), "%%", r"\\%%"), "_", r"\\_"))')
else:
expected_sql = (
"SELECT tests_author.name FROM tests_author WHERE " +
"REGEXP_CONTAINS(CAST(UPPER(tests_author.name) AS STRING), " +
"REPLACE(REPLACE(REPLACE(CONCAT('(?i)', (UPPER(%s))), " +
'"\\\\", "\\\\\\\\"), "%%", r"\\%%"), "_", r"\\_"))')
self.assertEqual(sql_compiled, expected_sql)
self.assertEqual(params, ("abc", ))
def test_iexact_sql_query_case_insensitive_function_transform(self):
qs1 = Author.objects.filter(
name__upper__iexact=F("last_name")).values("name")
compiler = SQLCompiler(qs1.query, self.connection, "default")
sql_compiled, params = compiler.as_sql()
if USING_DJANGO_3:
expected_sql = (
"SELECT tests_author.name FROM tests_author WHERE " +
"REGEXP_CONTAINS(UPPER(tests_author.last_name), " +
"CONCAT('^(?i)', CAST(UPPER(tests_author.name) AS STRING), '$'))"
)
else:
expected_sql = (
"SELECT tests_author.name FROM tests_author WHERE " +
"REGEXP_CONTAINS((UPPER(tests_author.last_name)), " +
"CONCAT('^(?i)', CAST(UPPER(tests_author.name) AS STRING), '$'))"
)
self.assertEqual(sql_compiled, expected_sql)
self.assertEqual(params, ())
