Django queryset equivalent to the following SQL

Question

I have a class :

class ImgObject(models.Model):
 img_file = models.CharField(max_length=128,unique=False)
 img_id = models.IntegerField(default=0)
 img_tags = models.CharField(max_length=320,unique=False)
 img_title = models.CharField(max_length=128,unique=False)

Each img_title will have corressponding img_tags. There might be multiple rows of img_title with different set of img_tag. For example,

img_title    img_tags
---------------------
buildingA    yellow
buildingB    seaside
buildingA    apartment,urban
buildingC    suburban
buildingA    yellow

I want my query to return the img_tags of each img_title along with the count of each tag. For example,

SELECT DISTINCT img_title,img_tags, COUNT(img_tags) FROM ImgObject

should return

buildingA : yellow(2),apartment(1),urban(1)
buildingB : seaside
buildingC : suburban

I understand that there should be some iterative process going on, but I am not able to make it into Django query. Here is what I tried,

for i in ImgObject.objects.values('img_title'):
    x = ImgObject.objects.filter('img_title').values('img_tags')
    y = ImgObject.objects.filter('img_title').values('img_tags').distinct().count()

print x,":",y

But this throws a "ValueError: too many values to unpack" error.

Help me write a code to achieve this output!

score 0 · Answer 1 · answered Sep 25 '15 at 17:19

0

Disclaimer: This code is untested

Try with this:

from django.db.models import Count
ImgObject.objects.annotate(c=Count('img_tags')).values('img_title').distinct()

answered Sep 25 '15 at 17:19

Gocht

9,924
3
42
81

I'm not sure about this. Does this gives the result of each img_title and how many img_tags does that img_title has? the OP wants the counts for each img_tag for each img_title, right? – Shang Wang Sep 25 '15 at 17:22
@ShangWang Yes, I've worked with a query like this to get Sum, I think this could do the job. I haven't tested. I don't have a db to test right now. – Gocht Sep 25 '15 at 17:27
This query gives the list of distinct img_title and but not the img_tags and their count. – Sudha M.R. Sep 26 '15 at 06:52

score 0 · Answer 2 · answered Sep 26 '15 at 17:58

So, after a lot of logical thinking, trial and error method I figured out a way to do this :)

for i in ImgObject.objects.values_list('img_title',flat=True).distinct():
          for j in ImgObject.objects.filter(img_title=str(i)).distinct().values_list('img_tags').annotate(the_count=Count('img_tags')):
                (j1,j2)= j
                print i,j1,j2

This doesn't completely work. It still returns the 1st column multiple times even though I mentioned distinct. So the output will look somewhat like this.

buildingA : yellow(2)
buildingA : apartment(1)
buildingA : urban(1)
buildingB : seaside
buildingC : suburban

instead of the preferred

buildingA : yellow(2),apartment(1),urban(1)
buildingB : seaside
buildingC : suburban

I still wish I could print it in the latter format. If anyone has an idea to do it, optimizations are most welcome :)

Django queryset equivalent to the following SQL

2 Answers2