tags:

views:

56

answers:

1

Hello guys:

here is my model


class gameUserTrophyInfo(models.Model):
   user    = models.ForeignKey(userInfo)
   trophy  = models.ForeignKey(gameTrophyInfo)
   date    = models.DateTimeField()

i want a query that returns to me: for all users, return the most recent trophy.

Example:

my db:

date (Day - Month - Year )


| user | trophy | date              |
| 1    |      1 | 10-10-10 10:00:00 |
| 1    |      2 | 10-10-10 09:00:00 |
| 2    |      1 | 10-10-10 01:00:00 |
| 2    |      2 | 11-10-10 01:00:00 |
| 3    |     10 | 20-10-10 01:00:00 |

returns:


| user | trophy | date              |
|    1 |      1 | 10-10-10 10:00:00 |
|    2 |      2 | 11-10-10 01:00:00 |
|    3 |     10 | 20-10-10 01:00:00 |

how can i do that ?

thanks !

+1  A: 

You need two queries:

users = User.objects.annotate(max_trophy_info_id=Max('gameusertrophyinfo__id'))
ids = [user.max_trophy_info_id for user in users]
trophy_infos = gameUserTrophyInfo.objects.filter(id__in = ids)
Zach
with this you are taking into consideration that a greater ID (gameusertrophyinfo__id) is equal to a more recent date, right?Well, this is not true in my db, would have a way to apply the Max column 'date'?Excuse me if I'm wrong interpreting your solution.thanks!
fabriciols
seems like you could replace that with `'gamusertrophyinfo__date'` for the correct result.
Daniel Roseman
with this, the last line fails:TypeError: int() argument must be a string or a number, not 'datetime.datetime'any sugestion ?
fabriciols