Construct a django queryset that orders an inner join

Question

I am trying to track page views in a django-based website. I have the following model

class PageView:
   date = DateTimeField( auto_now=True )
   user = ForeignKey( User )
   page = ForeignKey( Page )

to track each viewing of the page. I want to construct a queryset that returns a user's recently viewed Pages ordered by the date each page is viewed. For example, if I have the following page views:

date        | user_id | page_id | 
2011-02-20  |       5 |       2 |
2011-02-19  |       5 |       1 |
2011-02-24  |       5 |       1 |
2011-02-23  |       5 |       2 |

I want to the queryset to return

date        | page_id |  title    | and everything associated with the page
2011-02-24  |       1 |  "page 1" |
2011-02-23  |       2 |  "page 2" |

IN THAT ORDER!

In SQL, I can do it by

SELECT p.*, v.date FROM page p, 
(SELECT page_id, MAX(date) date FROM pageview 
WHERE user_id=5 GROUP BY page_id) AS v where p.id=v.page_id 
ORDER BY v.date;

But how can I construct it using django queryset API?

I will greatly appreciate any help!

Answer 1

Page.objects.filter(pageview__user = request.user).order_by('pageview__date')

For further reference in SQL joins see Documentation