Python django.db.models.expressions.RawSQL() Examples

def get_tag_order_by(self, tag):
        """Generate an OrderBy clause forcing JSON column->key to be used.

        This is only for helping to create a Window() for purposes of grouping
        by tag.

        Args:
            tag (str): The Django formatted tag string
                       Ex. pod_labels__key

        Returns:
            OrderBy: A Django OrderBy clause using raw SQL

        """
        descending = True if self.order_direction == "desc" else False
        tag_column, tag_value = tag.split("__")
        return OrderBy(RawSQL(f"{tag_column} -> %s", (tag_value,)), descending=descending) 

def order_by(self, *field_names):
        if not self.json_field:
            raise ValueError(
                'json_field cannot be blank, please provide a field on which to perform the ordering'
            )

        def build_json_order_by(field):
            try:
                if field.replace('-', '') not in NAMED_BLOCKS:
                    return field
            except AttributeError:
                return field

            if field[0] == '-':
                descending = True
                field = field[1:]
            else:
                descending = False
            db_table = self.model._meta.db_table
            return OrderBy(RawSQL(f'LOWER({db_table}.{self.json_field}->>%s)', (field,)), descending=descending, nulls_last=True)

        field_ordering = [build_json_order_by(field) for field in field_names]
        return super().order_by(*field_ordering) 

def test_raw_subquery(self):
        with self.assertNumQueries(0):
            raw_sql = RawSQL('SELECT id FROM auth_permission WHERE id = %s',
                             (self.t1__permission.pk,))
        qs = Test.objects.filter(permission=raw_sql)
        self.assert_tables(qs, Test, Permission)
        self.assert_query_cached(qs, [self.t1])

        qs = Test.objects.filter(
            pk__in=Test.objects.filter(permission=raw_sql))
        self.assert_tables(qs, Test, Permission)
        self.assert_query_cached(qs, [self.t1]) 

def get_recent_activity():
        """
        Lists the 12 most recent actions by users.
        """
        # Here we must use raw sql because the ORM is not quite able to take
        # a queryset, look up two separate foreign keys in two separate models
        # to get an object from a fourth model and return that to filter the
        # first queryset.
        sql = (
            "select id from private_sharing_activityfeed where "
            + "(member_id, project_id) IN (select member_id, project_id "
            + "from private_sharing_datarequestprojectmember "
            + "where visible='true')"
        )
        project_qs = ActivityFeed.objects.filter(id__in=RawSQL(sql, "")).filter(
            member__user__is_active=True
        )
        non_project_qs = ActivityFeed.objects.filter(project__isnull=True).filter(
            member__user__is_active=True
        )
        recent_qs = non_project_qs | project_qs
        recent = recent_qs.order_by("-timestamp")[0:12]
        recent_1 = recent[:6]
        recent_2 = recent[6:]
        return (recent_1, recent_2) 

def order_by_json_path(self, json_path, language_code=None, order='asc'):
        """
        Orders a queryset by the value of the specified `json_path`.

        More about the `#>>` operator and the `json_path` arg syntax:
        https://www.postgresql.org/docs/current/static/functions-json.html

        More about Raw SQL expressions:
        https://docs.djangoproject.com/en/dev/ref/models/expressions/#raw-sql-expressions

        Usage example:
            MyModel.objects.language('en_us').filter(is_active=True).order_by_json_path('title')
        """
        language_code = (language_code
                            or self._language_code
                            or self.get_language_key(language_code))
        json_path = '{%s,%s}' % (language_code, json_path)
        # Our jsonb field is named `translations`.
        raw_sql_expression = RawSQL("translations#>>%s", (json_path,))
        if order == 'desc':
            raw_sql_expression = raw_sql_expression.desc()
        return self.order_by(raw_sql_expression) 

def annotate_running_scans_count(self) -> 'ScanListQuerySet':
        return self.annotate(
            running_scans__count=RawSQL('''
                SELECT COUNT("{Scan}"."id")
                FROM "{Scan}"
                WHERE
                    "{Scan}"."end" IS NULL AND
                    "{Scan}"."site_id" IN
                        (SELECT "{Site_ScanLists}"."site_id"
                         FROM "{Site_ScanLists}"
                         WHERE "{Site_ScanLists}"."scanlist_id" = "{ScanList}"."id"
                         GROUP BY "{Site_ScanLists}"."site_id")
                '''.format(
                    Scan=Scan._meta.db_table,
                    Site_ScanLists=Site.scan_lists.through._meta.db_table,
                    ScanList=ScanList._meta.db_table), ())) 

def add_i18n_name(self, language):
        queryset = self._clone()
        alias = unique_together_left_join(queryset, ProblemTranslation, 'problem', 'language', language)
        return queryset.annotate(i18n_name=RawSQL('%s.name' % alias, ())) 

def test_coalesce_workaround_mysql(self):
        past = datetime(1900, 1, 1)
        now = timezone.now()
        Article.objects.create(title='Testing with Django', written=now)
        past_sql = RawSQL("cast(%s as datetime)", (past,))
        articles = Article.objects.annotate(
            last_updated=Greatest(
                Coalesce('written', past_sql),
                Coalesce('published', past_sql),
            ),
        )
        self.assertEqual(articles.first().last_updated, now) 

def test_coalesce_workaround_mysql(self):
        future = datetime(2100, 1, 1)
        now = timezone.now()
        Article.objects.create(title='Testing with Django', written=now)
        future_sql = RawSQL("cast(%s as datetime)", (future,))
        articles = Article.objects.annotate(
            last_updated=Least(
                Coalesce('written', future_sql),
                Coalesce('published', future_sql),
            ),
        )
        self.assertEqual(articles.first().last_updated, now) 

def test_rawsql_group_by_collapse(self):
        raw = RawSQL('SELECT MIN(id) FROM annotations_book', [])
        qs = Author.objects.values('id').annotate(
            min_book_id=raw,
            count_friends=Count('friends'),
        ).order_by()
        _, _, group_by = qs.query.get_compiler(using='default').pre_sql_setup()
        self.assertEqual(len(group_by), 1)
        self.assertNotEqual(raw, group_by[0]) 

def test_least_coalesce_workaround_mysql(self):
        future = datetime(2100, 1, 1)
        now = timezone.now()

        Article.objects.create(title="Testing with Django", written=now)

        future_sql = RawSQL("cast(%s as datetime)", (future,))
        articles = Article.objects.annotate(
            last_updated=Least(
                Coalesce('written', future_sql),
                Coalesce('published', future_sql),
            ),
        )
        self.assertEqual(articles.first().last_updated, now) 

def test_greatest_coalesce_workaround_mysql(self):
        past = datetime(1900, 1, 1)
        now = timezone.now()

        Article.objects.create(title="Testing with Django", written=now)

        past_sql = RawSQL("cast(%s as datetime)", (past,))
        articles = Article.objects.annotate(
            last_updated=Greatest(
                Coalesce('written', past_sql),
                Coalesce('published', past_sql),
            ),
        )
        self.assertEqual(articles.first().last_updated, now) 

def test_least_coalesce_workaround_mysql(self):
        future = datetime(2100, 1, 1)
        now = timezone.now()

        Article.objects.create(title="Testing with Django", written=now)

        future_sql = RawSQL("cast(%s as datetime)", (future,))
        articles = Article.objects.annotate(
            last_updated=Least(
                Coalesce('written', future_sql),
                Coalesce('published', future_sql),
            ),
        )
        self.assertEqual(articles.first().last_updated, truncate_microseconds(now)) 

def test_greatest_coalesce_workaround_mysql(self):
        past = datetime(1900, 1, 1)
        now = timezone.now()

        Article.objects.create(title="Testing with Django", written=now)

        past_sql = RawSQL("cast(%s as datetime)", (past,))
        articles = Article.objects.annotate(
            last_updated=Greatest(
                Coalesce('written', past_sql),
                Coalesce('published', past_sql),
            ),
        )
        self.assertEqual(articles.first().last_updated, truncate_microseconds(now)) 

def annotate_score(self, attr='zombodb_score'):
        db_table = connection.ops.quote_name(self.model._meta.db_table)
        return self.annotate(**{
            attr: RawSQL('zdb.score(' + db_table + '."ctid")', [])
        }) 

def add_problem_i18n_name(self, key, language, name_field=None):
        queryset = self._clone() if name_field is None else self.annotate(_name=F(name_field))
        alias = unique_together_left_join(queryset, ProblemTranslation, 'problem', 'language', language,
                                          parent_model=Problem)
        # You must specify name_field if Problem is not yet joined into the QuerySet.
        kwargs = {key: Coalesce(RawSQL('%s.name' % alias, ()),
                                F(name_field) if name_field else RawSQLColumn(Problem, 'name'),
                                output_field=models.CharField())}
        return queryset.annotate(**kwargs) 

def RawSQLColumn(model, field=None):
    if isinstance(model, Field):
        field = model
        model = field.model
    if isinstance(field, six.string_types):
        field = model._meta.get_field(field)
    return RawSQL('%s.%s' % (model._meta.db_table, field.get_attname_column()[1]), ()) 

def test_invalidate_nested_raw_subquery(self):
        permission = Permission.objects.first()
        with self.assertNumQueries(0):
            raw_sql = RawSQL('SELECT id FROM auth_permission WHERE id = %s',
                             (permission.pk,))
        with self.assertNumQueries(1):
            data1 = list(Test.objects.filter(
                pk__in=Test.objects.filter(permission=raw_sql)))
        self.assertListEqual(data1, [])

        test = Test.objects.create(name='test', permission=permission)

        with self.assertNumQueries(1):
            data2 = list(Test.objects.filter(
                pk__in=Test.objects.filter(permission=raw_sql)))
        self.assertListEqual(data2, [test])

        permission.save()

        with self.assertNumQueries(1):
            data3 = list(Test.objects.filter(
                pk__in=Test.objects.filter(permission=raw_sql)))
        self.assertListEqual(data3, [test])

        test.delete()

        with self.assertNumQueries(1):
            data4 = list(Test.objects.filter(
                pk__in=Test.objects.filter(permission=raw_sql)))
        self.assertListEqual(data4, []) 

def test_invalidate_raw_subquery(self):
        permission = Permission.objects.first()
        with self.assertNumQueries(0):
            raw_sql = RawSQL('SELECT id FROM auth_permission WHERE id = %s',
                             (permission.pk,))
        with self.assertNumQueries(1):
            data1 = list(Test.objects.filter(permission=raw_sql))
        self.assertListEqual(data1, [])

        test = Test.objects.create(name='test', permission=permission)

        with self.assertNumQueries(1):
            data2 = list(Test.objects.filter(permission=raw_sql))
        self.assertListEqual(data2, [test])

        permission.save()

        with self.assertNumQueries(1):
            data3 = list(Test.objects.filter(permission=raw_sql))
        self.assertListEqual(data3, [test])

        test.delete()

        with self.assertNumQueries(1):
            data4 = list(Test.objects.filter(permission=raw_sql))
        self.assertListEqual(data4, []) 

def queryset(self, request, queryset):
        events = EthereumEvent.objects.annotate(address=RawSQL("arguments->>'to'", ())
                                                ).values('address').distinct()
        if self.value() == 'HAS_TOKENS':
            return queryset.filter(address__in=events)
        elif self.value() == 'NO_TOKENS':
            return queryset.exclude(address__in=events) 

def queryset(self, request, queryset):
        if self.value() == 'FROM_SAFE_USER':
            param = 'from'
        elif self.value() == 'TO_SAFE_USER':
            param = 'to'
        else:
            return

        # Django doesn't support `->>` for auto conversion to text
        return queryset.annotate(address=RawSQL("arguments->>%s", (param,))
                                 ).filter(address__in=SafeContract.objects.values('address')) 

def erc20_tokens_with_balance(self, address: str) -> List[Dict[str, Any]]:
        """
        :return: List of dictionaries {'token_address': str, 'balance': int}
        """
        arguments_value_field = RawSQL("(arguments->>'value')::numeric", ())
        return self.erc20_events(
            address=address
        ).values('token_address').annotate(
            balance=Sum(Case(
                When(arguments__from=address, then=-arguments_value_field),
                default=arguments_value_field,
            ))
        ).order_by('-balance').values('token_address', 'balance') 

def annotate_most_recent_scan_result(self) -> 'SiteQuerySet':
        return self.annotate(last_scan__result=RawSQL('''
        SELECT "{ScanResult}"."result"
        FROM "{ScanResult}"
        WHERE
            "{ScanResult}"."scan_id"="{Site}"."last_scan_id"
        LIMIT 1
        '''.format(
                ScanResult=ScanResult._meta.db_table,
                Site=Site._meta.db_table), ())) 

def annotate_most_recent_scan_end_or_null(self) -> 'SiteQuerySet':
        return self.annotate(
            last_scan__end_or_null=RawSQL('''
                SELECT DISTINCT ON (site_id) "end"
                FROM "{Scan}"
                WHERE
                    site_id={Site}."id"
                ORDER BY "site_id", "end" DESC NULLS FIRST
                LIMIT 1
                '''.format(
                    Scan=Scan._meta.db_table,
                    Site=Site._meta.db_table,
                    Site_ScanLists=Site.scan_lists.through._meta.db_table), ())) 

def annotate_most_recent_scan_start(self) -> 'SiteQuerySet':
        return self.annotate(
            last_scan__start=RawSQL('''
                SELECT DISTINCT ON (site_id) "start"
                FROM "{Scan}"
                WHERE
                    site_id={Site}."id"
                ORDER BY "site_id", "end" DESC NULLS FIRST
                LIMIT 1
                '''.format(
                    Scan=Scan._meta.db_table,
                    Site=Site._meta.db_table,
                    Site_ScanLists=Site.scan_lists.through._meta.db_table), ())) 

def annotate_most_recent_scan_error_count(self) -> 'ScanListQuerySet':
        return self.annotate(
            last_scan__error_count=RawSQL('''
                SELECT COUNT("id")
                FROM "{ScanError}"
                WHERE
                    "{ScanError}"."scan_id" = "{Site}"."last_scan_id"
                '''.format(
                    Scan=Scan._meta.db_table,
                    Site=Site._meta.db_table,
                    ScanError=ScanError._meta.db_table), ())) 

def from_search_query(self, search_query: SearchQuery) -> QuerySet:
        """
        Return queryset of objects from SearchQuery.results, **in order**.

        EXPERIMENTAL: this will only work with results from a single index,
        with a single doc_type - as we are returning a single QuerySet.

        This method takes the hits JSON and converts that into a queryset
        of all the relevant objects. The key part of this is the ordering -
        the order in which search results are returned is based on relevance,
        something that only ES can calculate, and that cannot be replicated
        in the database.

        It does this by adding custom SQL which annotates each record with
        the score from the search 'hit'. This is brittle, caveat emptor.

        The RawSQL clause is in the form:

            SELECT CASE {{model}}.id WHEN {{id}} THEN {{score}} END

        The "WHEN x THEN y" is repeated for every hit. The resulting SQL, in
        full is like this:

            SELECT "freelancer_freelancerprofile"."id",
                (SELECT CASE freelancer_freelancerprofile.id
                    WHEN 25 THEN 1.0
                    WHEN 26 THEN 1.0
                    [...]
                    ELSE 0
                END) AS "search_score"
            FROM "freelancer_freelancerprofile"
            WHERE "freelancer_freelancerprofile"."id" IN (25, 26, [...])
            ORDER BY "search_score" DESC

        It should be very fast, as there is no table lookup, but there is an
        assumption at the heart of this, which is that the search query doesn't
        contain the entire database - i.e. that it has been paged. (ES itself
        caps the results at 10,000.)

        """
        hits = search_query.hits
        score_sql = self._raw_sql([(h["id"], h["score"] or 0) for h in hits])
        rank_sql = self._raw_sql([(hits[i]["id"], i) for i in range(len(hits))])
        return (
            self.get_queryset()
            .filter(pk__in=[h["id"] for h in hits])
            # add the query relevance score
            .annotate(search_score=RawSQL(score_sql, ()))  # noqa: S611
            # add the ordering number (0-based)
            .annotate(search_rank=RawSQL(rank_sql, ()))  # noqa: S611
            .order_by("search_rank")
        )