CODEWITHSUNDEEP
pythondjangodjango-queryset
Melissa Stewart
Melissa Stewart

Reputation: 3625

Complex ORM query in django

This is my User

class User(AbstractBaseUser, PermissionsMixin):
    email = models.EmailField(unique=True, max_length=255)
    mobile = PhoneNumberField(null=True)
    username = models.CharField(null=False, unique=True, max_length=255)
    full_name = models.CharField(max_length=255, blank=True, null=True)
    is_superuser = models.BooleanField(default=False)
    is_active = models.BooleanField(default=False)

And this is my Quiz model,

class Quiz(Base):
    category = models.ForeignKey(Category, related_name='quizzes', on_delete=models.CASCADE)
    winners = models.ManyToManyField(User, related_name='quizzes_won')
    losers = models.ManyToManyField(User, related_name='quizzes_lost')

I want to query all quizzes that an user has not played, thus winners and losers does not contain user id. How do I do this. Sorry I'm new to django.

Upvotes: 2

Views: 34

Answers (2)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477607

We can use .exclude(..) here and as criterion that the User is in the winners or losers relation. So:

from django.db.models import Q

Quiz.objects.exclude(Q(winners=user) | Q(losers=user))

Here Q [doc] is an object that encapsulated a certain predicate. By using the "or" operator | we thus specify as condition that winners contains user, or loser contains user. We exclude thus Quizzes that satisfy at least one (or both) of these criteria.

This results with a MySQL backend in a query like:

SELECT `quiz`.*
FROM `quiz`
WHERE NOT (
    (  `quiz`.`id` IN (SELECT U1.`quiz_id` AS Col1
                       FROM `quiz_winners` U1
                       WHERE U1.`user_id` = 123)
    OR `quiz`.`id` IN (SELECT U1.`quiz_id` AS Col1
                       FROM `quiz_losers` U1
                       WHERE U1.`user_id` = 123)
    )
)

(where 123 is in fact the id of the user)

Upvotes: 2

Daniel Roseman
Daniel Roseman

Reputation: 599956

You can use exclude:

Quiz.objects.exclude(winners=my_user).exclude(losers=my_user)

Upvotes: 2

Related Questions