Hoping you can help me.

I am trying to run the below - for ONLY the current requesting user. But it pulls back the data for all users.

Can you help me to figure out why that is?

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
            ''')

CodePudding user response：

As the Django documentation on raw(…) [Django-doc] says:

raw() always triggers a new query and doesn’t account for previous filtering. As such, it should generally be called from the Manager or from a fresh QuerySet instance.

You thus should include the user filtering in the raw query with:

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]
    )

Here we make use of the parameters that we can pass to the query [Django-doc]. One should not format the SQL string with the data, since that can result in SQL injection [wiki].