Re: Database API question: I am not able to return a QuerySet
Suriya <suriya <at> gmail.com>
2006-08-01 01:36:35 GMT
> 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
> 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 =
> 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
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
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):
> 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.
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