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.
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()