CODEWITHSUNDEEP
sqldjangodjango-modelsdjango-queryset
rrauenza
rrauenza

Reputation: 6963

Django: How to construct query that includes related latest?

I have a model that is doing something like this:

class Job(Model):
    ...

class Attempt(Model):
    job = ForeignKey(Job)
    attempt_number = PositiveIntegerField()

class StatusHistory(Model):
    attempt = ForeignKey(Attempt)
    state = CharField()

As the job goes through various states, a state is added to the history. As it goes through various attempts, an attempt is added.

Often I find myself wanting to construct a query that iterates over all of the jobs where the current (i.e., latest) attempt is in a particular (current, latest) state.

I know I can say job.attempt_set.latest() to get the latest attempt for a particular job, but being able to query for, say,

Job.objects.filter(attempt_set__latest__state_set__latest__state='final')

Just doesn't seem possible.

I am inclined to add another foreign key to Job or Attempt for the latest attempt/state and just keep it up to date in a transaction when I add an attempt/state history.

Thoughts? Is this a well known design pattern? What would you do?

Upvotes: 0

Views: 70

Answers (1)

augustomen
augustomen

Reputation: 9739

Sometimes, you just have to fallback to the good-and-old SQL using extra(). How would you resolve it?

Job.objects.extra(
    select={'lastest_state': '''(select state from app_statushistory T1
    join app_attempt T2 on T1.attempt_id=T1.id
    where T2.job_id=app_job.id
    order by id desc limit 1)'''},
    where=['lastest_state=%s'],
    params=['final'])

Replace any app_<tablename> ocurrences with the actual table names (and order by id with another sort if you have it).

Here are the docs for extra().

Upvotes: 1

Related Questions