Django Q objects and m2m queries

Question

I'm totally flummoxed by this behavior. I clearly don't understand Q objects like I thought I did or I'm doing something massively stupid and obvious. Here's what I'm running into. accepted_attendee_* are all m2m relationships to OfficialProfile. In the django shell, for ease of demonstration.

>>> profile = OfficialProfile.objects.get(user__username='testofficial3')
>>> r = SchedulerEvent.objects.filter(accepted_attendee_referee=profile)
>>> l = SchedulerEvent.objects.filter(accepted_attendee_linesman=profile)
>>> o = SchedulerEvent.objects.filter(accepted_attendee_official=profile)
>>> r
[<SchedulerEvent: andrew>]
>>> l
[]
>>> o
[]

This is all as expected. Now tho, if I combine together with a Q object, things get weird.

>>> qevents = SchedulerEvent.objects.filter(Q(accepted_attendee_referee=profile)|Q(accepted_attendee_official=profile)|Q(accepted_attendee_linesman=profile))
>>> qevents
[<SchedulerEvent: andrew>, <SchedulerEvent: andrew>]

Two objects are returned, both with the same PK - two duplicate objects. Should be only one, based on the individual queries. But once again, when I do this:

>>> r|l|o
[<SchedulerEvent: andrew>, <SchedulerEvent: andrew>]

What is it about this OR query that returns two objects when there should, I believe quite clearly, be only one?

EDIT

So I looked at the query that was produced and it seems like the "answer" has nothing to do with Q objects or the OR'ing at all; rather, it's the way the ORM joins the table. Here's the SQL and the results it generates, absent the OR:

mysql> SELECT `scheduler_schedulerevent`.`id`, `scheduler_schedulerevent`.`user_id`, `scheduler_schedulerevent`.`title`, `scheduler_schedulerevent`.`description`, `scheduler_schedulerevent`.`start`, `scheduler_schedulerevent`.`end`, `scheduler_schedulerevent`.`location_id`, `scheduler_schedulerevent`.`age_level_id`, `scheduler_schedulerevent`.`skill_level_id`, `scheduler_schedulerevent`.`officiating_system_id`, `scheduler_schedulerevent`.`auto_schedule`, `scheduler_schedulerevent`.`is_scheduled` 
FROM `scheduler_schedulerevent` 
LEFT OUTER JOIN `scheduler_schedulerevent_accepted_attendee_referee` 
ON ( `scheduler_schedulerevent`.`id` = `scheduler_schedulerevent_accepted_attendee_referee`.`schedulerevent_id` ) 
LEFT OUTER JOIN `scheduler_schedulerevent_accepted_attendee_linesman` 
ON ( `scheduler_schedulerevent`.`id` = `scheduler_schedulerevent_accepted_attendee_linesman`.`schedulerevent_id` ) 
LEFT OUTER JOIN `scheduler_schedulerevent_accepted_attendee_official` 
ON ( `scheduler_schedulerevent`.`id` = `scheduler_schedulerevent_accepted_attendee_official`.`schedulerevent_id` );

+----+---------+---------------+-------------+---------------------+---------------------+-------------+--------------+----------------+-----------------------+---------------+--------------+
| id | user_id | title         | description | start               | end                 | location_id | age_level_id | skill_level_id | officiating_system_id | auto_schedule | is_scheduled |
+----+---------+---------------+-------------+---------------------+---------------------+-------------+--------------+----------------+-----------------------+---------------+--------------+
|  1 |       1 | Test Event    |             | 2015-04-09 02:00:00 | 2015-04-09 02:30:00 |         161 |            1 |              1 |                     3 |             0 |            0 |
|  2 |       1 | Test          |             | 2015-04-07 20:00:00 | 2015-04-07 21:00:00 |         161 |            1 |              1 |                     3 |             1 |            0 |
|  3 |       1 | Test Auto     |             | 2015-04-07 20:00:00 | 2015-04-07 20:30:00 |         161 |            1 |              1 |                     2 |             0 |            0 |
|  4 |       1 | Test Official |             | 2015-04-16 19:00:00 | 2015-04-16 20:30:00 |         161 |            1 |              1 |                     3 |             0 |            1 |
|  4 |       1 | Test Official |             | 2015-04-16 19:00:00 | 2015-04-16 20:30:00 |         161 |            1 |              1 |                     3 |             0 |            1 |
+----+---------+---------------+-------------+---------------------+---------------------+-------------+--------------+----------------+-----------------------+---------------+--------------+

and then clearly, when you add an OR, it satisfies two query conditions based on the results of the join. So while adding distinct to the query seems unnecessary, it is quite necessary.

Answer 1

No you aren't being thick , but just accept that the underlying system (django's ORM) and Q objects will match 2 elements which are the same and therefore only one is actually matched. Q objects are very powerful when used correctly such as searching a query in a database. In this example you do not need Q objects but a simple filter would work fine.

Have you tried how a simple .filter(...) behaves in your case?

In the end you are not trying to understand why it is so that Q objects will return that queryset; you are trying to get a certain result, and distinct() works fine :)

Answer 2

If you are getting two objects and it is the same, is because that object satisfies at least two queryset.

In other words, <SchedulerEvent: andrew> satisfy with at least two queryset r l o

If you don't want duplicate objects, use .distinct() function.

SchedulerEvent.objects.filter(Q(accepted_attendee_referee=profile)|Q(accepted_attendee_official=profile)
|Q(accepted_attendee_linesman=profile)).distinct()