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 <http://lgiordani.github.com>
My profile on About.me <http://about.me/leonardo.giordani> - My GitHub
page<https://github.com/lgiordani>- My Coderwall
profile <https://coderwall.com/lgiordani>
2013/11/19 Jorge Cardoso Leitão <jorgecarlei...@gmail.com>
> 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 <http://stackoverflow.com/questions/4899014>, 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
>
>
>
>
> On Tue, Nov 19, 2013 at 8:29 AM, Leonardo Giordani <
> giordani.leona...@gmail.com> wrote:
>
>> May you please post the code of Book and Author models? Thanks
>>
>>
>> Leonardo Giordani
>> Author of The Digital Cat <http://lgiordani.github.com>
>> My profile on About.me <http://about.me/leonardo.giordani> - My GitHub
>> page <https://github.com/lgiordani> - My Coderwall
>> profile<https://coderwall.com/lgiordani>
>>
>>
>> 2013/11/16 J. C. Leitão <jorgecarlei...@gmail.com>
>>
>> Hi there.
>>>
>>> Consider the situation where you want to order_by a sum of two fields
>>> obtained from an annotation. This
>>> problem<http://stackoverflow.com/questions/4899014/using-extra-on-fields-created-by-annotate-in-django>
>>> 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<http://stackoverflow.com/questions/4899014/using-extra-on-fields-created-by-annotate-in-django>
>>> 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
>>>
>>> --
>>> 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+unsubscr...@googlegroups.com.
>>> To post to this group, send email to django-users@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.
>>>
>>
>> --
>> 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+unsubscr...@googlegroups.com.
>> To post to this group, send email to django-users@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/CAEhE%2BOk4ey8b3Opt32K082r%3Drr%2BFnH0Ne4QnyCUxRiB7dgW9Wg%40mail.gmail.com
>> .
>>
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>
> --
> 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+unsubscr...@googlegroups.com.
> To post to this group, send email to django-users@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/CAOYPqDDgqgjeGZBQFtWMdNw5UMAJ8NvsqL_itxrG2aQk-G3A7w%40mail.gmail.com
> .
>
> For more options, visit https://groups.google.com/groups/opt_out.
>
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@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/CAEhE%2BOn1c2NtOoTbr4w3CiQca__7LsvRcg-HhXHMh_%2Bu9jueyA%40mail.gmail.com.
For more options, visit https://groups.google.com/groups/opt_out.
