I see. What about Alec Shaner's suggestion? If you replace 'order_by' with
'latest' it will be similar to my suggestion with just two queries.
- Paulo
On Fri, Aug 13, 2010 at 8:28 AM, Emily Rodgers <emily.kate.rodg...@gmail.com
> wrote:
> 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>
> <django-users%2bunsubscr...@googlegroups.com<django-users%252bunsubscr...@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>
> <django-users%2bunsubscr...@googlegroups.com<django-users%252bunsubscr...@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.
