CODEWITHSUNDEEP
sqldjangodjango-orm
salezica
salezica

Reputation: 76949

Django: join, keep one of two columns, then order by that

I have two Django models, roughly summarized by:

class Thing(Model):
    date_created = DateTimeField()

class ThingDateOverride(Model):
    thing    = ForeignKey(Thing)
    category = ForeignKey(Category)
    override = DateTimeField()

What I want to do is produce a list of Things for a given a Category sorted by the appropriate ThingDateOverride override field, or the Thing's date_created if no such override exists.

In other words:

I can pull this off in SQL, but I'd rather avoid writing possibly engine-specific code. I'm currently implementing the logic around the ORM (in pure python), but I would like the database to handle this.

How can I do it?

Upvotes: 2

Views: 667

Answers (2)

demalexx
demalexx

Reputation: 4761

Is it ok, that category is stored in ThingDateOverride? If yes, then Thing object doesn't have category without override object.

I assumed that ThingDateOverride object exists for every Thing object (so category is assigned to every Thing object). override field can be NULL, then date_created object will be used to sort things. Then this code should sort by override if it exists, or by date_created if doesn't:

Thing.objects.filter(thingdateoverride__category=category).extra(select={'d': 'if(override, override, date_created)'}).order_by('d')

The idea is to use extra and select override field if it exists, or date_created if doesn't as another column, and then sort by that column.

Note: this code works only because override and date_created fields have different names, so they could be distinguished. Otherwise MySQL will return error, something like "field name is ambiguous", and need to add table names.

Upvotes: 1

trez
trez

Reputation: 478

If you want Thing, you should start by that model and use reverse relationship:

Thing.objects.filter(thingdateoverride__category=category).order_by("thingdateoverride__override", "date_created")

Upvotes: 0

Related Questions