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

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

On Tue, Nov 19, 2013 at 8:29 AM, Leonardo Giordani <giordani.leonardo@gmail.com> wrote:

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.

--
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/CAOYPqDDgqgjeGZBQFtWMdNw5UMAJ8NvsqL_itxrG2aQk-G3A7w%40mail.gmail.com.
For more options, visit https://groups.google.com/groups/opt_out.