Django Order By Generic Foreign Key

Question

Hey, this is a quick one. I have two models, Post and Term and I'd like to be able to tag and categorize (taxonomy) posts as well as other (future) models. My Post model has the following fields: title, content, published (date) and my Term is declared like this:

class Term(models.Model):
    taxonomy = models.CharField(max_length=255)
    name = models.CharField(max_length=255)
    slug = models.SlugField(max_length=50)

Then I have a TermRelation model that sticks Terms to Posts and other models, like this:

class TermRelation(models.Model):
    term = models.ForeignKey(Term)
    object_id = models.PositiveIntegerField()
    content_type = models.ForeignKey(ContentType)
    content_object = generic.GenericForeignKey()

Everything works as expected but my question is the following. I'd like to create an archive of posts in a certain category, and order the posts by their publish date. This is what I'm trying to do:

ctype = ContentType.objects.get_for_model(Post)
relations = TermRelation.objects.filter(content_type__pk=ctype.id)

And it works fine, although it's sorted by the relation PK I guess. When I try to do the following:

relations = TermRelation.objects.filter(content_type__pk=ctype.id).order_by('content_object__published')

I get an error saying that there's no content_object field in TermRelation. I know there must be a way to solve it. Any ideas?

Thanks ~ K

Answer 1

As far as i know this is not possible with django, because the order_by-arguments are translated to database columns/tables and content_object doesn't exit in the database like this. This is also the reason why you have to do a content type lookup with object_id/content_type...

Answer 2

Yeah, I was quite right about the simple SQL query with a join. A few joins actually. Here's what worked for me (django >= 1.2 for the raw() method)

data = {
    'posts': Post._meta.db_table,
    'relations': TermRelation._meta.db_table,
    'terms': Term._meta.db_table,
    'tag_id': tag.id
}

posts = Post.objects.raw('SELECT %(posts)s.* FROM %(posts)s JOIN %(relations)s ON %(posts)s.id = %(relations)s.object_id JOIN %(terms)s ON %(relations)s.term_id = %(terms)s.id WHERE %(terms)s.id = %(tag_id)s ORDER BY %(posts)s.published DESC' % data)

Wasn't that difficult. Maybe I should wrap it up in a method to TermRelation, what do you think?

Update: I did wrap it up into a static method and made it more universal to deal with different content types and sort orders. It does still assume that there's a field called content_type_id which is created by default if there's a foreign key on ContentType. Here's the code:

@staticmethod
def get_objects_by_term_id(model=None, taxonomy=None, term_id=None, order_by='NULL'):
    data = {
        'objects': model._meta.db_table,
        'content_type': ContentType.objects.get_for_model(model).id,
        'relations': TermRelation._meta.db_table,
        'terms': Term._meta.db_table,
        'term_id': term_id,
        'order_by': ' ORDER BY %s ' % order_by
    }

    return model.objects.raw('SELECT %(objects)s.* FROM %(objects)s JOIN %(relations)s ON %(objects)s.id = %(relations)s.object_id AND %(relations)s.content_type_id = %(content_type)s JOIN %(terms)s ON %(relations)s.term_id = %(terms)s.id WHERE %(terms)s.id = %(term_id)s %(order_by)s' % data)