Django: remove GROUP BY added with extra() method ?

Question

Hi (excuse me for my bad english) !

When I make this:

gallery_qs = Gallery.objects.all()\
                    .annotate(Count('photos'))\
                    .extra(select={'photo_id': 'photologue_photo.id'})

The sql query is :

SELECT (photologue_photo.id) AS `photo`, `photologue_gallery`.*
FROM `photologue_gallery` 
    LEFT OUTER JOIN `photologue_gallery_photos` 
      ON (`photologue_gallery`.`id` = `photologue_gallery_photos`.`gallery_id`) 
    LEFT OUTER JOIN `photologue_photo` 
      ON (`photologue_gallery_photos`.`photo_id` = `photologue_photo`.`id`) 
GROUP BY `photologue_gallery`.`id`, photologue_photo.id 
ORDER BY `photologue_gallery`.`publication_date` DESC

The problem is that extra method automatically adds photologue_photo.id in GROUP BY clause. And I need remove it, because it duplicates galleries, for example :

[<Gallery: Lorem ipsum dolor sit amet>, <Gallery: Lorem ipsum dolor sit amet>, <Gallery: Lorem ipsum dolor sit amet>, <Gallery: Lorem ipsum dolor sit amet>, <Gallery: Lorem ipsum dolor sit amet>, <Gallery: Lorem ipsum dolor sit amet>, <Gallery: Lorem ipsum dolor sit amet>]

Si I need to make this query with django, is it possible ?

SELECT (photologue_photo.id) AS `photo`, `photologue_gallery`.*
FROM `photologue_gallery` 
    LEFT OUTER JOIN `photologue_gallery_photos` 
      ON (`photologue_gallery`.`id` = `photologue_gallery_photos`.`gallery_id`) 
    LEFT OUTER JOIN `photologue_photo` 
      ON (`photologue_gallery_photos`.`photo_id` = `photologue_photo`.`id`) 
GROUP BY `photologue_gallery`  
ORDER BY `photologue_gallery`.`publication_date` DESC

Thank you ! :)

Answer 1

I don't think you really need the extra. From Django's concept, you don't need to cherry pick specific columns while running a Django QuerySet. That logic can be done in the template side.

I assume you know how to push galley_qs to your template from your view:

# views.py
gallery_qs = Gallery.objects.all()\
                .annotate(Count('photos'))

In your template/html:

{% for gallery in gallery_qs %}
    {% for photo in gallery.photos %}

    {% endfor %}
{% endfor %}

photos is your ManyToManyField in your gallery model.

Answer 2

Why are you trying to get distinct gallery records with a photo_id annotated on them when gallery to photo ids is a many to many relationship? From what I can tell, the query you are doing would only get a single photo id for each gallery.

If you really do need to do the above, I think you could use distinct() to get distinct gallery records (btw, you don't need the "all()" in there).

Gallery.objects.distinct()\
               .annotate(Count('photos'))\
               .extra(select={'photo_id': 'photologue_photo.id'})

Or you could just simply access the photo id directly,

g = Gallery.objects.annotate(Count('photos'))
# Get the photo
photo = g.photos[0]