order_by result of sum of fields computed from an annotate

[J. C. Leitão]

Hi there.

Consider the situation where you want to order_by a sum of two fields obtained from an annotation. This problem states it correctly, however, the solution is not working for me. 

For concreteness, consider the example:

Author.objects.annotate(first_date=Avg('all_books__published_date'),

                                   last_date=Avg('all_books__added_date'))\

                     .extra(select = {'delta_time': 'last_date - first_date'},

                              order_by=['-delta_time'])

Where:

- Book has a manyToMany relationship with Author and 'all_books' is the related_name from the book to the author.

- *_date are dates.

I.e. For each author, I'm computing the average time it takes for a book to be added (e.g. to a library) since the moment it was published.

This query is not correct as "(1054, "Unknown column 'last_date' in 'field list'")", which I suspect is because the field all_books is a ManyToMany.

The solution I found so far was an extra like this:

Author.objects.extra(

    select = {'delta_time': 'AVG(all_books__added_date) - AVG(all_books__published_date)'},

    order_by = ['-delta_time']

)

but in this case this is not working with the error ""Unknown column 'all_books__added_date'", which I suspect is also because the field is a ManyToMany.

Any ideas on how to create this query?

Thanks,

Jorge

[Leonardo Giordani]

May you please post the code of Book and Author models? Thanks

Leonardo Giordani

Author of The Digital Cat
My profile on About.me - My GitHub page - My Coderwall profile

2013/11/16 J. C. Leitão <jorgeca...@gmail.com>

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/104ab2a3-17e7-4726-be87-0ac353d957ca%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Jorge Cardoso Leitão]

I'm sorry, you are right, I should have provided them. Let me formulate the question again, but with a complete example.

# models.py

class Book(models.Model):

    added_date = models.DateField()        # date it was added to my shelf

    published_date = models.DateField()  # date it was published

    authors = models.ManyToManyField('Author', related_name='all_books')  # a book can have more than 1 author

class Author(models.Model):

    name = models.CharField(max_length=100)

#fixture.json

[

    {

        "model": "main.Author",

        "pk": 1,

        "fields": {

            "name": "John 1"

        }

    },

    {

        "model": "main.Author",

        "pk": 2,

        "fields": {

            "name": "John 2"

        }

    },

    {

        "model": "main.Book",

        "pk": 1,

        "fields": {

            "added_date": "2000-01-01",

            "published_date": "1999-12-31",

            "authors": [1]

        }

    },

    {

        "model": "main.Book",

        "pk": 1,

        "fields": {

            "added_date": "2000-01-02",

            "published_date": "1999-12-31",

            "authors": [1, 2]

        }

    }

]

Notice that book 1 has 1 author, and it took 1 day to add to the shelf. Book 2 has 2 authors, and took 2 days to add.

The average delay between publishing and adding is thus, for each author,

John 1: 1.5 days  # (1 day + 2 days)/2

John 2: 1 day.

I'm trying to query authors with the average delay annotated, and ordered by it.

Notice that the average of the difference is the difference of the average and thus,

I can write a query to average each date, and compute the difference of the averages. This is how I'm doing it:

# inside a view.

d = Author.objects.annotate(first_date=Avg('all_books__published_date'),

                                                   last_date=Avg('all_books__added_date')) \

                                 .extra(select = {'average_delay': 'last_date - first_date'}, order_by=['-average_delay'])

print d  # to evaluate query

In a clean installation of Django, with sqlite3, with the models and fixture I provide here, I get a No such column: last_date error.

If I remove the .extra, this query works (but doesn't give the result I want).

Comment: I find strange that I'm not able to select "last_date" in the extra().

In this question, this problem is addressed. The accepted solution is using 

d = Author.objects.extra({'average_delay': 'AVG(all_books__added_date) - AVG(all_books__published_date)'})

print d

In my setup, this gives: no such column: all_books__published_date

Question: What am I missing?

Cheers,

Jorge

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAEhE%2BOk4ey8b3Opt32K082r%3Drr%2BFnH0Ne4QnyCUxRiB7dgW9Wg%40mail.gmail.com.

[Leonardo Giordani]

Well, indeed the matter is not simple =)

I looked at the raw SQL for your queries and the results are the following (just print queryset.query to show SQL).

The SQL query for

Author.objects.extra(select={'average_delay': "Avg('all_books__published_date')-Avg('all_books__added_date')"})

is

SELECT (Avg('all_books__published_date')-Avg('all_books__added_date')) AS "average_delay", "prova_author"."id", "prova_author"."name" FROM "prova_author"

so you see that it cannot work, as you stated. Django does not recognize that extra is working on a joined table, and this because the string passed to select in extra are unparsed. Documentation states that "Django inserts the given SQL snippet directly into the SELECT statement".

The other solution you tried is

Author.objects.annotate(first_date=Avg('all_books__published_date'), last_date=Avg('all_books__added_date')).extra(select={'average_delay': "last_date - first_date"})

that produces the following SQL

SELECT (last_date - first_date) AS "average_delay", "prova_author"."id", "prova_author"."name", AVG("prova_book"."added_date") AS "last_date", AVG("prova_book"."published_date") AS "first_date" FROM "prova_author" LEFT OUTER JOIN "prova_book_authors" ON ("prova_author"."id" = "prova_book_authors"."author_id") LEFT OUTER JOIN "prova_book" ON ("prova_book_authors"."book_id" = "prova_book"."id") GROUP BY "prova_author"."id", "prova_author"."name", (last_date - first_date)

If I correctly remember SQL (not my forte) you cannot refer in the SELECT clause to aliases that you define in the SELECT itself.

There is some onging work in Django to allow F() objects in annotations so this perhaps will solve such issues.

Until then I'd suggest you to do the following: the difference of averages is the average of the differences (in this case, since the number of object is the same for the first and the second average), so you can store in your object the difference between published and insertion date, overriding the save() method. Then you annotate and filter on the Avg() of this new field.

Try and let me know if it works. Regards

Leo

Leonardo Giordani

Author of The Digital Cat
My profile on About.me - My GitHub page - My Coderwall profile

2013/11/19 Jorge Cardoso Leitão <jorgeca...@gmail.com>

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAOYPqDDgqgjeGZBQFtWMdNw5UMAJ8NvsqL_itxrG2aQk-G3A7w%40mail.gmail.com.