Database Query

Using Django
1

Hi all,

I have a fairly complex query that I’ve been trying to create using Django’s query abstraction language, but so far no luck.

I have 2 tables I need to query one of which is auth_user. The other table I’ll call contact. The contact table has two columns each of which are foreign keys to the auth_user table (user_id, user_accept_id). The contact table establishes relationships between people on the website.

What I need to build is a query that retrieves people I am NOT connected to.

Here is the equivalent query in raw sql, my id will be 1 for the sake of the query below.

select u.id, u.username, resultset.id, resultset.myid, resultset.cid
from auth_user as u
left join (
select id, user_id as myid, user_accept_id as cid from polls_contacts where user_id=1
union
select id, user_accept_id as myid, user_id as cid from polls_contacts where user_accept_id=1
group by myid, cid
) as resultset on u.id=resultset.cid
where
u.id != 1 and resultset.id IS NULL;

I’m considering just using the method to write this query as raw, but would rather find out how to use the abstraction layer, if possible.

Thanks,
Chris

2

I think this is what you want:

In [1]: user_id = 1
   ...: contact_users = Contact.objects.filter(user_id=user_id).values_list('user_accept_id')
   ...: accepted_users = Contact.objects.filter(user_accept_id=user_id).values_list('user_id')
   ...: unconnected_users = User.objects.exclude(id__in=contact_users.union(accepted_users)).exclude(id=user_id)

In [2]: str(unconnected_users.query)
Out[2]: 'SELECT "core_user"."id" FROM "core_user" WHERE (NOT ("core_user"."id" IN (SELECT "core_contact"."user_accept_id" FROM "core_contact" WHERE "core_contact"."user_id" = 1 UNION SELECT "core_contact"."user_id" FROM "core_contact" WHERE "core_contact"."user_accept_id" = 1)) AND NOT ("core_user"."id" = 1))'

It seesm your Contacts tabel is working as a many-to-many relationship - are you not using a ManyToManyField?

1 Like
3

Yes, my contacts table is working as a manytomany relationship.

Thanks for the query I will give this a try.

Cheers.

4

Thanks Adam your query did indeed work. So the query you provided me looked at the system set of users, and displays those systems users which do not already belong to me.

However, now I need to get the users that belong to me (I’ve used the same union statement for this) and exclude a subset of users in the system that belong to a ‘project’.

So the website logic is first connect with my group of contacts. Now out of this group of contacts I can add them to projects.

My steps are:
1 - get the users belonging to the project
2 - use the same union logic to get my contacts
3 - exclude the project users from the group of my contacts, so that I have a nice clean list of users that I might want to add to the project

However, it seems that django does not let filter/exclude happen on union’d queries. Any ideas on how I can achieve this subset of my contacts that do NOT belong to a given project?

Here are the queries i tried and the error I received:
user_id=1
project_id=1

rs_project_users = ProjectUsers.objects.filter(project_id=project_id).exclude(user_id=user_id).values_list(‘user_id’)
rs_contacts = Contacts.objects.filter(user_id=user_id).values_list(‘user_accept_id’).union(Contacts.objects.filter(user_accept_id=user_id).values_list(‘user_id’))

rs_contacts.exclude(user_id__in=rs_project_users)

|Django Version:|3.0.1|
|Exception Type:|NotSupportedError|
|Exception Value:|Calling QuerySet.exclude() after union() is not supported.|

Any more insight you can provide would be appreciated.
Cheers.

5

Just an FYI I’ve decided to rearrange my table structure to simplify queries. Thanks!

6

To avoid the error you should apply the same exclude() on each queryset before union()ing them together.