Re: Database API question: I am not able to return a QuerySet
Suriya <suriya <at> gmail.com>
2006-08-01 01:36:35 GMT
DavidA wrote:
> Suriya,
>
> You will probably have to do this in custom SQL or using extra(). Your
> query requires a subselect to get the "current B's" (B's with max(date)
> for each A).
>
> Here's the SQL that I think you need (if I understand the problem
> correctly):
>
> select * from <app>_A join <app>_B on <app>_B.a_id = <app>_A.id
> where <app>_B.date = (select max(date) from <app>_B where a_id =
> <app>_B.a_id)
> and <app>_B.status = 1
I think your SQL query provides what I need. For no rational
reason, I am trying to avoid custom SQL as much as I can.
This is what I have currently got
class ValidAsManager(models.Manager):
def get_query_set(self):
q = super(ValidAsManager, self).get_query_set()
lst = [ i.id for i in q if i.status() == 1 ]
# The call to filter() below does not work if lst
# is empty, and I am handling that (not shown
# here)
return q.filter(id__in=lst)
This is obviously inefficient, but returns a QuerySet.
> I think that maps to
>
> A.objects.extra(where=['<app>_B.a_id = (select max(date) from <app>_B
> where a_id = <app>_B.a_id)'], tables=['<app>_B']).filter(b__status=1)
>
> You might want to consider modeling this differently. I have a similar
> problem where I'm essentially tracking different versions of an object.
> But instead of just using one date, I use two for the range that the
> version was valid: date_from and date_thru. For the current version, I
> set date_thru to null. Then a query of the current versions is really
> easy: filter(date_thru__isnull=True). You can also see all versions at
> a given point in time with the slightly more complex (but efficient):
> filter(date_from__lte=some_date).filter(Q(date_thru__gt=some_date)|Q(date_thru__isnull=True))
>
> In your design all of these types of queries require a subselect. Of
> course, its more work to keep my table up to date, but I have the need
> to query it arbitrarily in many ways so paying a little expense at
> insert time (once per quarter) for better query performance (many times
> per day) is a good tradeoff, in my case.
It is a good idea to have two fields to help keep track
of which row is the latest. I have decided to keep the
table simple, so that the users who enter data do
not have to be educated. Or else, I will have to write
a custom interface for updating the status.
Thank you for your ideas.
Suriya
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django-users <at> googlegroups.com
To unsubscribe from this group, send email to django-users-unsubscribe <at> googlegroups.com
For more options, visit this group at http://groups.google.com/group/django-users
-~----------~----~----~----~------~----~------~--~---
RSS Feed