Complex ORM query in django

Question

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.

Not played means Quiz with no winners and no losers? right? – Lemayzeur Jun 10 '18 at 21:21 — Lemayzeur, Jun 10 '18 at 21:21
@Lemayzeur yes exactly. – Melissa Stewart Jun 10 '18 at 21:22 — Melissa Stewart, Jun 10 '18 at 21:22

score 2 · Answer 1 · answered Jun 10 '18 at 21:22

2

You can use exclude:

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

answered Jun 10 '18 at 21:22

Daniel Roseman

588,541
66
880
895

QQ, can I do ```Quiz.objects.exclude(winners=my_user, losers=my_user)``` – Melissa Stewart Jun 10 '18 at 21:25
1

@MelissaStewart: no, that would mean quizzes where `user` is both a `winner` and `loser`. – Willem Van Onsem Jun 10 '18 at 21:26
So effectively my syntax means AND, and yours means OR, right? – Melissa Stewart Jun 10 '18 at 21:27
1

@MelissaStewart: indeed, if you put multiple conditions (separated with comma), by default Django uses "and" logic. Technically speaking there is nothing wrong if they had used "or" logic, but somehow they decided to use "and" (non of the two is inherently better). – Willem Van Onsem Jun 10 '18 at 21:28

Willem Van Onsem · Accepted Answer · 2018-06-10T22:24:15.557

2

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)

edited Jun 10 '18 at 22:24

answered Jun 10 '18 at 21:22

Willem Van Onsem

443,496
30
428
555

@W QQ what's Q, could you please point me to the documentation – Melissa Stewart Jun 10 '18 at 21:24
@MelissaStewart:: see edit (with reference to the documentation). – Willem Van Onsem Jun 10 '18 at 21:26
@MelissaStewart this answer is better to use instead of two `exclude` – Lemayzeur Jun 10 '18 at 21:28

Complex ORM query in django

2 Answers2