getting objects unique in several ways (annotate?)

[elliot]

Sorry about the vague title. Its hard for me to summarize this in one
short sentence.
Lets say I have two models (using pseudo code):

Book(model.Model):
name = charfield()

Transaction(model.Model):
start_date = DateTimeField()
item = ManytoMany(Book)
recipient = CharField()

Now for every book, I want to know the recipient of only the most
recent transaction. I can get the start date of the most recent
transaction for each book with

Book.objects.annotate(mostrecent = Max('transaction__start_date'))

but what I would like is the pk of that transaction, so I can get more
information about it. Can this be done even in multiple queries?
Must I hack a solution using lists instead of queries?

I have simplified my question quite a bit, I hope not too much.

thanks for reading,
Elliot

[Michał Sawicz]

Dnia 2011-06-29, śro o godzinie 16:31 -0700, elliot pisze:

> Book.objects.annotate(mostrecent = Max('transaction__start_date'))

.values('pk')

Or whatever values you want.
--
Michał Sawicz <mic...@sawicz.net>

[bruno desthuilliers]

On Jun 30, 1:31 am, elliot <offonoffoffon...@gmail.com> wrote:
> Sorry about the vague title.  Its hard for me to summarize this in one
> short sentence.
> Lets say I have two models (using pseudo code):
>
> Book(model.Model):
>   name = charfield()
>
> Transaction(model.Model):
>   start_date = DateTimeField()
>   item = ManytoMany(Book)
>   recipient = CharField()
>
> Now for every book, I want to know the recipient of only the most
> recent transaction.  I can get the start date of the most recent
> transaction for each book with
>
> Book.objects.annotate(mostrecent = Max('transaction__start_date'))
>
> but what I would like is the pk of that transaction, so I can get more
> information about it.  

Sorry no time to find out and provide a working anwser, but anyway:
you may want to read the doc for QuerySet.extra. Also, sometimes it's
just a matter of starting from the other side of the relationship...

HTH

[elliot]

Michal's answer does not work. For each book, I want fields from one
of its Transactions. .values('pk') gives the book's pk (no
relationship to Transaction) and .values('transaction__recipient')
does not work either.

I dont know squat about writing queries in sql, so extra doesn't help
me. When coming at it from the other side of the relationship, I
can't see how to get just one transaction for each book. Doing it the
above way, the database finds the one transaction per book, but wont
tell me the pk or any other information about that transaction.
Coming from the other side, it would be easy to order the transactions
by book__pk and then date, and then use a foor loop to grab the most
recent transaction for each book.

Using the for loop, I'll need to make sure to not hit the db for every
iteration. Otherwise, I dont think this would be intolerably slow.

[elliot]

incase anyone cares, this is the solution I came up with. an initial
query to filter for one condition, and then a for loop that checks
that each member satisfies the second condition. hopefully,
selected_related makes sure only one db call is made:

(in a function)
books =
Book.objects.filter(whateverconditions).distinct().select_related('transaction')
incirc = []
for b in books:
recenttran = b.transaction_set.order_by('-start_date')[0]
if recenttran.recipient == self:
incirc.append(b)