Python expressions.RawSQL方法代码示例

# 需要导入模块: from django.db.models import expressions [as 别名]
# 或者: from django.db.models.expressions import RawSQL [as 别名]
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), ())) 

# 需要导入模块: from django.db.models import expressions [as 别名]
# 或者: from django.db.models.expressions import RawSQL [as 别名]
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) 

# 需要导入模块: from django.db.models import expressions [as 别名]
# 或者: from django.db.models.expressions import RawSQL [as 别名]
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) 

# 需要导入模块: from django.db.models import expressions [as 别名]
# 或者: from django.db.models.expressions import RawSQL [as 别名]
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) 

# 需要导入模块: from django.db.models import expressions [as 别名]
# 或者: from django.db.models.expressions import RawSQL [as 别名]
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) 

# 需要导入模块: from django.db.models import expressions [as 别名]
# 或者: from django.db.models.expressions import RawSQL [as 别名]
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), ())) 

# 需要导入模块: from django.db.models import expressions [as 别名]
# 或者: from django.db.models.expressions import RawSQL [as 别名]
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), ())) 

# 需要导入模块: from django.db.models import expressions [as 别名]
# 或者: from django.db.models.expressions import RawSQL [as 别名]
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), ())) 

# 需要导入模块: from django.db.models import expressions [as 别名]
# 或者: from django.db.models.expressions import RawSQL [as 别名]
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), ())) 

# 需要导入模块: from django.db.models import expressions [as 别名]
# 或者: from django.db.models.expressions import RawSQL [as 别名]
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') 

# 需要导入模块: from django.db.models import expressions [as 别名]
# 或者: from django.db.models.expressions import RawSQL [as 别名]
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')) 

# 需要导入模块: from django.db.models import expressions [as 别名]
# 或者: from django.db.models.expressions import RawSQL [as 别名]
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]) 

# 需要导入模块: from django.db.models import expressions [as 别名]
# 或者: from django.db.models.expressions import RawSQL [as 别名]
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, []) 

# 需要导入模块: from django.db.models import expressions [as 别名]
# 或者: from django.db.models.expressions import RawSQL [as 别名]
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, []) 

# 需要导入模块: from django.db.models import expressions [as 别名]
# 或者: from django.db.models.expressions import RawSQL [as 别名]
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]), ())