Django: limit queryset to a condition in the latest instance of a related set

Question

Hi!

I have an hierarchy of models that consists of four levels, all for various good reasons but which to explain would be beyond the scope of this question, I assume.
So here it goes in pseudo python:

class Base(models.Model):
    ...

class Top(models.Model):
    base = FK(Base)

class Middle(models.Model):
    top = FK(Top)
    created_at = DateTime(...)
    flag = BooleanField(...)

class Bottom(models.Model):
    middle = FK(Middle)
    stored_at = DateTime(...)
    title = CharField(...)

Given a title, how do I efficiently find all instances of Base for which that title is met only in the latest (stored_at) Bottom instance of the latest (created_at) Middle instance that has flag set to True?

I couldn't find a way using the ORM, and the way I've seen it, .latest() isn't useful to me on the model that I want to query. The same holds for any convenience methods on the Base model. As I'm no SQL expert, I'd like to make use of the ORM as well as avoid denormalization as much as possible.

Thanks!

Answer 1

So, apparently, without heavily dropping into (some very unwieldy) SQL and not finding any alternative solution, I saw myself forced to resort to denormalized fields on the Base model, just as many as were required for efficiently getting the wanted (filtered) querysets of said model.

These fields were then updated at creation/modificatin time of respective Bottom instances.