How can I make a Django query for the first occurrence of a foreign key in a column?

Question

Basically, I have a table with a bunch of foreign keys and I'm trying to query only the first occurrence of a particular key by the "created" field. Using the Blog/Entry example, if the Entry model has a foreign key to Blog and a foreign key to User, then how can I construct a query to select all Entries in which a particular User has written the first one for the various Blogs?

class Blog(models.Model):
    ...

class User(models.Model):
    ...

class Entry(models.Model): 
    blog = models.Foreignkey(Blog)
    user = models.Foreignkey(User)

I assume there's some magic I'm missing to select the first entries of a blog and that I can simple filter further down to a particular user by appending:

query = Entry.objects.magicquery.filter(user=user)

But maybe there's some other more efficient way. Thanks!

Answer 1

query = Entry.objects.filter(user=user).order_by('id')[0]

Basically order by id (lowest to highest), and slice it to get only the first hit from the QuerySet.

I don't have a Django install available right now to test my line, so please check the documentation if somehow I have a type above:

order by

limiting querysets

By the way, interesting note on 'limiting queysets" manual section:

To retrieve a single object rather than a list (e.g. SELECT foo FROM bar LIMIT 1), use a simple index instead of a slice. For example, this returns the first Entry in the database, after ordering entries alphabetically by headline:

Entry.objects.order_by('headline')[0]

EDIT: Ok, this is the best I could come up so far (after yours and mine comment). It doesn't return Entry objects, but its ids as entry_id.

query = Entry.objects.values('blog').filter(user=user).annotate(Count('blog')).annotate(entry_id=Min('id'))

I'll keep looking for a better way.

Answer 2

Can't test it in this particular moment

Entry.objects.filter(user=user).distinct("blog").order_by("id")