id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
14971	Exclude by annotation works as OR rather than AND	orblivion	nobody	"I'm using sqlite3. I believe that this is all the relevant code; I'd rather not share everything I'm doing so I changed names and redacted irrelevant identifying characteristics to protect the innocent.

Here's the model code:

{{{

class ObjGroup(models.Model):
    pass

class Revision(models.Model):
    obj_group = models.ForeignKey(ObjGroup)

class Obj(models.Model):
    created_obj_revision = models.ForeignKey(Revision, related_name = ""new_objects"")
    rejected_obj_revision = models.ForeignKey(Revision, related_name = ""rejected_objects"", null = True, blank = True)

}}}

Now I'm trying, in the shell at the moment, to query all Revisions in an Obj_Group where there's at least one object in revision.new_objects or revision.rejected_objects. So I figure what I should do is annotate the Count of new_objects and the Count of rejected_objects, and then exclude where 
both are zero. Exclude is supposed to work as NOT ( condition AND condition ), which I tested works properly on other attributes. But on these annotations, it seems to work as NOT( condition OR condition ).


First list the count for all revisions in the group

{{{

 In : qs = obj_group.revision_set.annotate(
      num_rejected_objects = Count(""rejected_objects""), num_new_objects = Count(""new_objects"")).all()

 In : [(q.num_new_objects, q.num_rejected_objects) for q in qs]

 Out : [(2, 0),
  (0, 1),
  (1, 0),
  (0, 1),
  (1, 1),
  (0, 0),
  (1, 0),
  (0, 1),
  (1, 0),
  (2, 2),
  (0, 0),
  (1, 1),
  (0, 0),
  (1, 0),
  (0, 0)]

}}}

Now let's exclude them

{{{

 In : qs = dg.revision_set.annotate(
      num_rejected_objects = Count(""rejected_objects""), num_new_objects = Count(""new_objects"")).exclude(
      num_rejected_objects = 0, num_new_objects=0)

 In : [(q.num_new_objects, q.num_rejected_objects) for q in qs]

 Out: [(1, 1), (2, 2), (1, 1)]

}}}
It's eliminated all zeroes, rather than just the cases where both are zero

Now just for fun let's filter them instead

{{{
 In : qs = dg.revision_set.annotate(
      num_rejected_objects = Count(""rejected_objects""), num_new_objects = Count(""new_objects"")).filter(
      num_rejected_objects = 0, num_new_objects=0)

 In : [(q.num_new_objects, q.num_rejected_objects) for q in qs]

 Out: [(0, 0), (0, 0), (0, 0), (0, 0)]

}}}

It properly selected only the ones where both are zero.

Let me know if you need any more details about my setup. Thanks."		closed	Database layer (models, ORM)	1.2		duplicate			Unreviewed	0	0	0	0	0	0