Query API: get all users without a group

[Thomas Guettler]

Hi,

I want to get all objects where the corresponding many-to-many field is
empty.

Example: get all users without a group

My solution:
User.objects.exclude(groups__in=Group.objects.all())

I think this is not optimal, since Group.objects.all() gets evaluated.

Resulting SQL:

SELECT "auth_user"."id", "auth_user"."username",
"auth_user"."first_name", "auth_user"."last_name",
"auth_user"."email", "auth_user"."password", "auth_user"."is_staff",
"auth_user"."is_active", "auth_user"."is_superuser",
"auth_user"."last_login", "auth_user"."date_joined" FROM "auth_user" WHERE
NOT ("auth_user"."id" IN (SELECT U1."user_id" FROM "auth_user" U0
INNER JOIN "auth_user_groups" U1 ON (U0."id" = U1."user_id")
WHERE U1."group_id" IN (9, 10, 11, 8))) LIMIT 21

Any better way to do this?

Should the extra() method be used for this?

Thomas

--
Thomas Guettler, http://www.thomas-guettler.de/
E-Mail: guettli (*) thomas-guettler + de

[Filip Wasilewski]

On 2 Gru, 16:45, Thomas Guettler <h...@tbz-pariv.de> wrote:
> Hi,
>
> I want to get all objects where the corresponding many-to-many field is
> empty.
>
> Example:  get all users without a group

[...]

The preferred solution is using the `isnull` filter:

User.objects.filter(groups__isnull=True)

It evaluates to a left join of the `auth_user` table with
`auth_user_groups` and a simple `where` statement.

fw

[alex....@gmail.com]

You could do User.objects.exclude(groups__in=Group.objects.all
().query) which will actually generate a subquery.

Alex

> Thomas Guettler,http://www.thomas-guettler.de/

[Thomas Guettler]

Filip Wasilewski schrieb:

Thank you! Django ORM is great.