Re: order_by result of sum of fields computed from an annotate

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 <jorgecarleitao@gmail.com>

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

--
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+unsubscribe@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+unsubscribe@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.