On Wed, Apr 29, 2009 at 4:08 PM, omat <o...@gezgin.com> wrote:
>
> Hi all,
>
> I would like to fetch the latest entries for each user from a model
> like:
>
> class Entry(models.Model):
> user = models.ForeignKey(User)
> entry = models.TextField()
> date = models.DateTimeField(auto_now_add=True)
>
> Is it possible with the current API with a single query?
Not easily. You can get the date of the most recent entry for each user:
Entry.objects.values('user').annotate(Max('date'))
but you can't get the full entry object for each date without using
extra() or doing some other join gymnastics.
> I.e., wouldn't it be great to have something like:
>
> Entry.objects.annotate(Max('added'), group_by=['user']).filter
> (date=date__max)
And what does this mean in SQL? Once you've posed your original
question in SQL, you may see why your proposal isn't really viable.
If you're a MySQL user, I'm sure your initial suggestion will be something like:
SELECT id, user_id, entry, MAX("date") FROM entries GROUP BY user_id
The problem is that while this is legal for MySQL (and SQLite), this
isn't legal SQL on other backends (notably Postgres; I'm pretty sure
Oracle is in the same boat). The problem is that Postgres requires
that every field that isn't an aggregate be part of the GROUP BY
clause. This is for a very good reason - which value for id and entry
should be included in the result set?
If the problem isn't clear, consider the following slightly modified query:
SELECT id, user_id, entry, MAX("date"), MIN("date") FROM entries GROUP
BY user_id
Now - which entry and ID should be included in the results? The one
corresponding to the MAX or the MIN?
The problem here is that SQLite and MySQL are lax when it comes to the
relational algebra. For simple cases, this laxness may not matter, but
for non-trivial cases it matters a great deal.
To address the group_by suggestion specifically - we deliberately
avoided adding explicit support for group_by. Django's ORM attempts to
look like a collection of Objects, not a wrapper around SQL. GROUP BY
is a very SQL heavy concept that doesn't have much meaning with other
backends.
In order to do this in a purely relational sense, you need to use an
inner query to build the table of userid-max date, then join this
result with the original entries to select the rows where the user ids
and dates match. However, queries like this aren't easy to compose in
using pure Django ORM syntax.
Yours,
Russ Magee %-)
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
