CODEWITHSUNDEEP
pythondjangodjango-modelsdjango-queryset
sergzach
sergzach

Reputation: 6764

How to select users which is not paired with a custom user through many-to-one?

I have 2 models - WaitingUser and MarkedUserIds.

class WaitingUser( models.Model ):
    user_id = models.IntegerField()
    class Meta:
        db_table = 'waiting_user'

class MarkedUserIds( models.Model ):
    user = models.ForeignKey( WaitingUser ) # a user who was marked
    marked_user_id = models.IntegerField() # another user (user_id) who marked the user
    class Meta:
        db_table = 'marked_user_ids'

WaitingUser is for storing users which want to be marked. One user can mark another user one time. If a user marked by another user then corresponding row exists in MarkedUserIds.

I want to find all users which was not marked by a specific one (I have user_id of the user).

The corresponding SQL query:

SELECT 
    user_id 
FROM 
    waiting_user 
LIMIT 
    1
WHERE
    user_id NOT IN 
    ( SELECT user_id FROM marked_user_ids WHERE marked_user_id={specific_one_user_id})
AND 
    user_id != {specific_one_user_id}

How to create a corresponding Django query (without raw SQL)? Thank you very much.

Clarification: I can reorganize models if it's required.

Upvotes: 0

Views: 44

Answers (1)

McAbra
McAbra

Reputation: 2524

Make a list of ids you don't want:

excluded_ids = [user.id for user in MarkedUserIds.objects.filter(marked_user_id = {specific_one_user_id})]

Then get Users excluding those ids:

WaitingUser.objects.exclude(user_id__in = excluded_ids)

EDIT:
In one query:

WaitingUser.objects.exclude(id__in = MarkedUserIds.objects.filter(marked_user_id = {specific_one_user_id}).values_list('id', flat=True))

I may not be understanding your filter conditions correctly.

Hope it helps.

Upvotes: 1

Related Questions