5

Having the following model:

from django_hstore import hstore
from django.db import models

class Item(VoteModel):
    data = hstore.DictionaryField(db_index=True)
    objects = hstore.HStoreManager()

Something like:

Item.objects.extra(select={"key": "content_item.data -> 'key'"}).aggregate(Count('key'))

doesn't work, cf. Using .aggregate() on a value introduced using .extra(select={...}) in a Django Query? and https://code.djangoproject.com/ticket/11671.

The raw SQL which works is the following:

SELECT content_item.data -> 'key' AS key, count(*)  FROM content_item GROUP BY key;                                                                               
   key     | count 
-----------+-------
 value1    |   223
 value2    |    28
 value3    |    31
(3 rows)

How can I get hte same results through Django's ORM ?

FYI:

Item.objects.extra(select={"key": "content_item.data -> 'key'"})

translates to:

SELECT (content_item.data -> 'key') AS "key", "content_item"."id", "content_item"."data" FROM "content_item"
Community
  • 1
  • 1
Maxime R.
  • 8,043
  • 6
  • 50
  • 58

1 Answers1

6

Have you tried with values and order_by?

Item.objects.extra(
    select=dict(key = "content_item.data -> 'key'")
).values('key').order_by('key').annotate(total=Count('key'))

Something like this works for me in PostgreSQL and Django 1.4.

Paulo Scardine
  • 60,096
  • 9
  • 116
  • 138
  • 2
    Ahaa, clever, thanks a lot ! I knew I could get a list with `values('key')` but also failed trying to use `aggregate` on it. I'll have to spend some more time to fully understand how those orm abstractions work :) – Maxime R. Sep 21 '12 at 09:35
  • @MaximeR.: because of the the "admin" app dependency on the ORM I have to swallow it, but IMHO the ORM the weakest component in the Django framework. I dream with someone cloning the Django admin app using Flask, SQLAlchemy and Jinja2. – Paulo Scardine Sep 21 '12 at 09:54
  • @AlexK: for me `django.contrib.admin` is a killer app (not Django itself), I should spare some time and make a clone decoupled from any specific ORM. – Paulo Scardine Nov 29 '13 at 04:08