Thanks for the detailed reply Russ.
I added a boolean field to the model to specify the most current entry
for the user, updated with save(), thus letting me to fetch records of
the most current entries with a simple query.
I think de-normalization and avoiding expensive complex aggregate
queries is the most civilized way to go.
Best,
oMat
On Apr 29, 3:12 pm, Russell Keith-Magee <freakboy3...@gmail.com>
wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
