Can you sequentially add the states for each foo? I'm thinking of something
like:
states = []
for foo in foos:
foo_state = State.objects.filter(foo=foo, first_dt__lte=dt,
last_dt__gte=dt)
if foo_state:
states.append(foo_state)
else:
states = State.objects.filter(foo=foo, last_dt__lte=dt):
states.append(state.latest)
Of course you would have to define latest in the model Meta.
- Paulo
On Thu, Aug 12, 2010 at 8:26 PM, Alec Shaner <asha...@chumpland.org> wrote:
> Hopefully some django sql guru will give you a better answer, but I'll take
> a stab at it.
>
> What you describe does sound pretty tricky. Is this something that has to
> be done in a single query statement? If you just need to build a list of
> objects you could do it in steps, e.g.:
>
> # Get all State objects that span the requested dt
> q1 = State.objects.filter(first_dt__lte=dt, last_dt__gte=dt)
>
> # Get all State objects where foo is not already in q1, but have a last_dt
> prior to requested dt
> q1_foo = q1.values_list('foo')
> q2 =
> State.objects.exclude(foo__in=q1_foo).filter(last_dt__lt=dt).order_by('-last_dt')
>
> But q2 would not have unique foo entries, so some additional logic would
> need to be applied to get the first occurrence of each distinct foo value in
> q2.
>
> Probably not the best solution, but maybe it could give you some hints to
> get started.
>
>
> On Thu, Aug 12, 2010 at 12:51 PM, Emily Rodgers <
> emily.kate.rodg...@gmail.com> wrote:
>
>> 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<django-users%2bunsubscr...@googlegroups.com>
>> .
>> For more options, visit this group at
>> http://groups.google.com/group/django-users?hl=en.
>>
>>
> --
> 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<django-users%2bunsubscr...@googlegroups.com>
> .
> For more options, visit this group at
> http://groups.google.com/group/django-users?hl=en.
>
--
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.
