时间戳

首页 > 解决方案 > 将 Django 数据限制为当前用户

python-3.x - 将 Django 数据限制为当前用户

问题描述

希望你能帮助我。

我正在尝试运行以下内容 - 仅适用于当前请求用户。但它会为所有用户拉回数据。

你能帮我弄清楚这是为什么吗？

open_tasks = skills.objects.filter(creator=request.user).raw('''
            SELECT *, round(((closed_points)/(open_points+closed_points)*100),2) as points_pct,
            round(((closed_count)/(open_count+closed_count)*100),2) as closed_pct from (
            SELECT id, sum(open_points) as open_points, sum(closed_points) as closed_points, sum(open_count) as open_count, sum(closed_count) as closed_count
            from (
            SELECT id, 
            case when status = 'open' then sum(points) end as open_points,
            case when status <> 'open' then sum(points) end as closed_points,
            case when status = 'open' then sum(count) end as open_count,
            case when status <> 'open' then sum(count) end as closed_count
            from (
            SELECT category as id, status, sum(cast(points as int)) as points, count(*) as count 
            FROM voximisa_skills group by category, status)s
            group by id, status)p
            group by id)j
            ''')

标签： python-3.xdjangodjango-models

解决方案

正如raw(…)[Django-doc]上的 Django 文档所说：

raw()总是触发一个新的查询并且不考虑以前的过滤。因此，通常应该从新实例Manager或从新QuerySet实例调用它。

因此，您应该在原始查询中包含用户过滤：

open_tasks = skills.objects.filter(creator=request.user).raw('''
            SELECT *, round(((closed_points)/(open_points+closed_points)*100),2) as points_pct,
            round(((closed_count)/(open_count+closed_count)*100),2) as closed_pct from (
            SELECT id, sum(open_points) as open_points, sum(closed_points) as closed_points, sum(open_count) as open_count, sum(closed_count) as closed_count
            from (
            SELECT id, 
            case when status = 'open' then sum(points) end as open_points,
            case when status <> 'open' then sum(points) end as closed_points,
            case when status = 'open' then sum(count) end as open_count,
            case when status <> 'open' then sum(count) end as closed_count
            from (
            SELECT category as id, status, sum(cast(points as int)) as points, count(*) as count 
            FROM voximisa_skills
            WHERE creator_id=%s
            GROUP BY category, status)s
            group by id, status)p
            group by id)j''',
        [request.user.pk]
    )

在这里，我们使用可以传递给查询 [Django-doc] 的参数。不应使用数据格式化 SQL 字符串，因为这可能导致SQL 注入[wiki]。

推荐阅读