Hi,
I am a bit stuck on this and can't seem to figure out what to do.
I have a model that (stripped down for this question) looks a bit like
this:
class State(models.Model):
first_dt = models.DateTimeField(null=True)
last_dt = models.DateTimeField(null=True)
foo = models.CharField(FooModel)
bar = models.ForeignKey(BarModel, null=True)
meh = models.ForeignKey(MehModel, null=True)
This is modeling / logging state of various things in time (more
specifically a mapping of foo to various other bits of data). The data
is coming from multiple sources, and what information those sources
provide varies a lot, but all of them provide foo and a date plus some
other information.
What I want to do, is given a point in time, return all the 'states'
that span that point in time. This seems trivial except for one thing
- a state for a particular 'foo' may still be persisting after the
last_dt until the next 'state' for that 'foo' starts. This means that
if there are no other 'states' between the point in time and the start
of the next state for a given foo, I want to return that state.
I have built a query that kindof explains what I want to do (but
obviously isn't possible in its current form):
dt = '2010-08-12 15:00:00'
lookups = State.objects.filter(
Q(
Q(first_dt__lte=dt) & Q(last_dt__gte=dt) |
Q(first_dt__lte=dt) &
Q(last_dt=State.objects.filter(foo=F('foo')).filter(first_dt__lte=dt).latest('last_dt'))
)
)
I know this doesn't work, but I think it illustrates what I am trying
to do better than words do.
Does anyone have any advice? Should I be using annotate or something
to show what the last_dt for each foo is? I might be being really
stupid and completely missing something but I have been trying to
figure this out for too long!
Cheers,
Emily
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
