Re: Database API question: I am not able to return a QuerySet

2006-07-31 Thread Suriya 

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 _A join _B on _B.a_id = _A.id
> where _B.date = (select max(date) from _B where a_id =
> _B.a_id)
> and _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=['_B.a_id = (select max(date) from _B
> where a_id = _B.a_id)'], tables=['_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@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users
-~--~~~~--~~--~--~---

Re: Database API question: I am not able to return a QuerySet

2006-07-31 Thread SmileyChris 


Suriya wrote:

> This returns the list of rows in table A that have status
> in table B set to 1 at some point in the past. What I want
> is the latest status from table B.

I see. Yes, you can either use a (grouping) custom SQL query or
refactor.

How I would do it is to have just one model (B) and an optional
foreignkey to self which indicates this has been replaced. If the FK is
blank, it is a current object (otherwise it is a historical object)


--~--~-~--~~~---~--~~
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@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users
-~--~~~~--~~--~--~---

Re: Database API question: I am not able to return a QuerySet

2006-07-31 Thread DavidA 

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 _A join _B on _B.a_id = _A.id
where _B.date = (select max(date) from _B where a_id =
_B.a_id)
and _B.status = 1

I think that maps to

 A.objects.extra(where=['_B.a_id = (select max(date) from _B
where a_id = _B.a_id)'], tables=['_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.

-Dave

Suriya wrote:
> SmileyChris wrote:
> > How about just making the query like this:
> >
> > A.objects.filter(b__status=1)
>
> This returns the list of rows in table A that have status
> in table B set to 1 at some point in the past. What I want
> is the latest status from table B. For example, if table B
> has the two entries:
>B(id=1, a=1, status=1, date=yesterday) and
>B(id=2, a=1, status=0, date=today)
> a = 1 should not be returned in the list, because the current
> status is 0.
> 
> 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@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users
-~--~~~~--~~--~--~---

Re: Database API question: I am not able to return a QuerySet

2006-07-31 Thread Suriya 

SmileyChris wrote:
> How about just making the query like this:
>
> A.objects.filter(b__status=1)

This returns the list of rows in table A that have status
in table B set to 1 at some point in the past. What I want
is the latest status from table B. For example, if table B
has the two entries:
   B(id=1, a=1, status=1, date=yesterday) and
   B(id=2, a=1, status=0, date=today)
a = 1 should not be returned in the list, because the current
status is 0.

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@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users
-~--~~~~--~~--~--~---

Re: Database API question: I am not able to return a QuerySet

2006-07-30 Thread SmileyChris 

How about just making the query like this:

A.objects.filter(b__status=1)


--~--~-~--~~~---~--~~
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@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users
-~--~~~~--~~--~--~---

Database API question: I am not able to return a QuerySet

2006-07-30 Thread Suriya 

Hi all,

I have a situation here where I do not know how to use
the filter() function in the database API to obtain a
QuerySet. I am describing the schema and what I have
done, below. I hope you have an answer to my question.

# Only the necessary fields are shown here
class A(models.Model):
pass

class B(models.Model):
a  = models.ForeignKey(A)
status = models.IntegerField('Status', blank=False)
date   = models.DateField('Date',  blank=False)
class Meta:
get_latest_by = 'date'

Whenever there is a change in the status of a row in
table A, a new row is added to table B, to reflect that
change. The latest row in table B corresponding to an
A, represents the current status of that A. It is
possible that there exists an A, which has no entry in
table B.

Now, what I want is a QuerySet of A's whose current
status is 1.

As of now, this is what I am doing:

class ValidAsManager(models.Manager):
"""does not return a QuerySet"""
def get_query_set(self):
return (i for i in super(ValidAsManager, self).get_query_set()
 if i.status() == 1)

class A(models.Model):

objects = models.Manager()
current_objects = ValidAsManager()

def status(self):
try:
s = self.b_set.latest()
return s.status
except ObjectDoesNotExist:
return 0

I am not able to use generic views for listing the
valid A's because ValidAsManager does not return a
QuerySet. Could someone tell me what I can do to return
a QuerySet? How can I use the filter function to do so?

If you feel my schema is incorrect, I can consider
changing it.

Thanks,
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@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users
-~--~~~~--~~--~--~---