Filter in django: the last post of each user -

i have 2 models, author , post , how can make filter can select last post (by id field) of each author in 1 line?, bad approach me :

authors = author.objects.all() queryset = [] author in authors:     posts = post.objects.filter(author=author).order_by('-id')     if loc:         queryset.append(posts[0]) 

specifically, filter tastypie resource "postresource", filter can give me last post of each user, ordered creation

complete solution okm answer , tastypie custom filter:

class locationresource(modelresource):     user = fields.foreignkey(accountresource,'user' )     class meta:         queryset = location.objects.all().order_by('-id')         resource_name = 'location'         #excludes = ['id',]         list_allowed_methods = ['post','get']         authentication = apikeyauthentication()         authorization= authorization()         filtering = {'user': all_with_relations}      def obj_create(self, bundle, **kwargs):         if bundle.request.method == 'post':             return super(locationresource, self).obj_create(bundle, user=bundle.request.user)      def apply_authorization_limits(self, request, object_list):         return object_list.filter(user=request.user)      def dehydrate(self, bundle):         return bundle      def build_filters(self, filters=none):         if filters none: #if don't pass filters @             filters = {}          orm_filters = super(locationresource, self).build_filters(filters)          if('only_lasts' in filters):              query = filters['only_lasts']              sqs = location.objects.values('user_id').annotate(max_id=models.max('id')).values('max_id')              orm_filters["pk__in"] = sqs          return orm_filters 

read the blog post fetching top row group in sql.

as described in post, use either in or join.

take in example:

select * post_table  id in (select max(id) max_id post_table group author_id); 

the sql written in queryset as:

post.objects.filter(pk__in=     post.objects.order_by().values('author_id').annotate(         max_id=models.max('id')).values('max_id')) 

the queryset pattern of select max(id) max_id post_table group author_id is:

model.objects.order_by().values(...).annotate(foo=...).values('foo') ^------------^----------^-----------^-----------------^------------^     \            \           \            \                \ base queryset     \        group        \           select column or manager      remove possible           annotation part                 useless ordering          min/max/... 

also, wrap sql in queryset.raw() directly. if choose join version, .raw() way clearer.

note in clause might have performance issue in db backend, need profile , tweak index if performance critical.