On Aug 12, 10:00 pm, Paulo Almeida <igcbioinformat...@gmail.com>
wrote:
> 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
The thing is, there could be say 3000 distinct foos in the table. It
would take ages to return the results if I did this. I was hoping to
do it in one query. Might have to revert to SQL if I can't do it using
the django ORM.
>
> 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.
