Django: retrieve all galleries containing one public photo at least

Question

Hi (please, excuse me for my ugly english) !

Imagine these very simple models :

class Photo(models.Model):
    is_public = models.BooleanField('Public', default=False)

class Gallery(models.Model):
    photos = models.ManyToManyField('Photos', related_name='galleries', null=True, blank=True)

I need to select all Gallery instances which contain at least one public photo (and if possible adding a photos__count attribute which contains the number of public photos).

I tried this query :

Gallery.objects.all()\
    .annotate(Count('photos'))\
    .filter(photos__is_public=True)

It seems to be okay, but : - the query is strange - the added attribute photos__count on each gallery will contain the total number of photos on this gallery, instead of the number of public photos in this gallery.

I thin that the hard-coded sql query I need is that :

SELECT `gallery`.* , COUNT(`gallery_photos`.`photo_id`) 
FROM `gallery` 
    INNER JOIN `gallery_photos` ON (`gallery`.`id` = `gallery_photos`.`gallery_id`) 
    INNER JOIN `photo` ON (`gallery_photos`.`photo_id` = `photo`.`id`) 
WHERE `photo`.`is_public` = True 
GROUP BY gallery.id ;

Any idea to fix it ?

Thank you ! ;-)

Answer 1

This should do it:

Edit, updated to add count:

SELECT `gallery`.*, 'a'.'count' 
FROM `gallery` 
inner join (
    select `gallery`.`id`, count(*) as count
    from `gallery_photos` 
    INNER JOIN `photo` ON (`gallery_photos`.`photo_id` = `photo`.`id`) 
    where `photo`.`is_public` = True
    group by `gallery`.`id`
) a on `gallery`.`id` = 'a'.'id'
WHERE `photo`.`is_public` = True

Answer 2

I would try:

Gallery.objects.filter(photos__is_public=True).annotate(Count('photos'))

I believe you just got your filter ordering wrong but I have not set up your models to test that assumption.

Try number two:

Gallery.objects.exclude(photos__is_public=False).annotate(Count('photos'))

That should be exclude all galleries where none of the photos are public and return a count of what is and isn't public.

Answer 3

This?

Gallery.objects.filter(photos__is_public=True)\
               .annotate(Count('photos__is_public'))

Answer 4

The django documentation at

http://docs.djangoproject.com/en/dev/topics/db/aggregation/#order-of-annotate-and-filter-clauses

and my experience say that the following query:

Gallery.objects.filter(photos__is_public=True).annotate(Count('photos'))

would give you galleries with at least one photo that is public and a count of only photos that are public. The only thing is that it will exclude galleries with zero public photos but it sounds like you don't care about that. Have you tested the above query?

If it still doesn't return the right data then annotate is probably changing the returned data by causing it to return only galleries where all are public. In that case you could use the "extra" method to get the count you want.

Gallery.objects.filter(photos__is_public=True).extra(select={
  "photo_count": """
  SELECT COUNT(`gallery_photos.id`)
      FROM `gallery_photos`
  WHERE `gallery_photos.gallery_id` `gallery.id AND
        `gallery_photos.is_public = True
  """})

Jason Christa's exclude method may also work.