On Aug 12, 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?
It doesn't have to be, but I would like to try to use a single query
that I can index the database for if possible.
> 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.
>
Thanks. I may have to do this if I can't figure out a single
statement.
> 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.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
