django orm equivalent for "group by sth. having every(sth.)"

Question

Suppose I have a model "Book" with three fields: name, author_name (as a string), publish_state. Now I want to find out which authors have exactly one published book. This sql query does exactly what I want:

SELECT author_name from books GROUP BY author_name HAVING count(name) = 1 and every(publish_state = 'published');

I'm using Postgres as you can say by the 'every' clause.

Is there a way to do this in django ORM? or do I need to use a raw query for that?

score 0 · Answer 1 · answered Apr 15 '14 at 18:54

0

There is probably a better way, but at least you can do the following ...

books = Book.objects.filter(publish_state='published')
authors = {}

for book in books:
    authors.setdefault(book.author, 0)
    authors[book.author] += 1

rookies = [x for x, y in authors.iteritems() if y == 1]

answered Apr 15 '14 at 18:54

jcfollower

3,103
19
25

Yes. This is technically correct, but I was looking for the database approach. – Mohammadreza MontazeriShatoori Apr 16 '14 at 17:15

score 0 · Answer 2 · answered Apr 15 '14 at 19:16

0

Django uses the having clause when you use filter() after an annotate like annotate(foo=Count('bar')).filter(foo__gt=3). Add in values_list and distinct like so and you should be good:

Book.objects.filter(publish_state='published').values_list('author_name',  flat=True).order_by('author_name').annotate(count_books=Count('name')).filter(count_books=1).distinct()

answered Apr 15 '14 at 19:16

patsweet

1,548
10
12

No. It is not correct! Your query contains those authors who have many books, but only one of them is published. I need those who have only one book, and that one is published. – Mohammadreza MontazeriShatoori Apr 16 '14 at 17:21
Then just move the filter for publish_state to after the annotation filter. – patsweet Apr 16 '14 at 19:35
Unfortunately (but not surprisingly), Django puts those filters (which are after the `annotate`) in the `WHERE` clause too, not the `HAVING` clause which I need. – Mohammadreza MontazeriShatoori Apr 17 '14 at 07:00

score 0 · Accepted Answer · answered Nov 18 '14 at 22:29

0

Unfortunately this feature is not implemented in django's ORM, and I don't think it would be any time soon, as it is not a very common query.

I had to use raw SQL. You can find about it here: https://docs.djangoproject.com/en/1.7/topics/db/sql/

answered Nov 18 '14 at 22:29

Mohammadreza MontazeriShatoori

99
8

django orm equivalent for "group by sth. having every(sth.)"

3 Answers3