Re: OneToOneField direction? (was Re: QuerySet.count() inaccurate across ForeignKey relationships)

[George Vilches]

Karen Tracey wrote:
> On 11/1/07, *George Vilches* <[EMAIL PROTECTED] 
> > wrote:
> [snip]
> 
> For reporting purposes though, we would like to be able to
> .select_related() on User, and get a cached copy of each of the OneToOne
> relationships.  It seems reasonable by the very essence of OneToOne, but
> I don't know if there's some limitation that would prevent that
> following from happening.  However, when we pull 50 users, having 50*N
> tables of extra queries when we need data from a few separate places
> makes the whole task unappealing to use the Django ORM for.  I wouldn't
> think about asking that this should be in the ORM, except that Django
> supports reverse foreign keys so intelligently that it seems a direct
> 1:1 correlation between rows is intuitive to go in either direction.
> 
> 
> In the absence of explicit support for the reverse relation on OneToOne 
> fields, can't you use the QuerySet extra() method ( 
> http://www.djangoproject.com/documentation/db-api/#extra-select-none-where-none-params-none-tables-none)
>  
> to pull in all the various tables/columns you are interested in in a 
> single query? 

We have used this in a few places already, and it's fine for just 
manipulating the data directly, but we really wanted to have instances 
of the Models themselves populated from that single query.  The models 
have a bunch of nice methods for manipulating the data, and it would be 
annoying to have to manually create instances of 10+ models and insert 
40+ columns of data into them to get to use those helper methods.  Too 
much typing. :)

 > p.s. to the Django website maintainers: pretty favicon!

Agreed, I like it.


George

--~--~-~--~~~---~--~~
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?hl=en
-~--~~~~--~~--~--~---

Re: OneToOneField direction? (was Re: QuerySet.count() inaccurate across ForeignKey relationships)

[Karen Tracey]

On 11/1/07, George Vilches <[EMAIL PROTECTED]> wrote:
[snip]

> For reporting purposes though, we would like to be able to
> .select_related() on User, and get a cached copy of each of the OneToOne
> relationships.  It seems reasonable by the very essence of OneToOne, but
> I don't know if there's some limitation that would prevent that
> following from happening.  However, when we pull 50 users, having 50*N
> tables of extra queries when we need data from a few separate places
> makes the whole task unappealing to use the Django ORM for.  I wouldn't
> think about asking that this should be in the ORM, except that Django
> supports reverse foreign keys so intelligently that it seems a direct
> 1:1 correlation between rows is intuitive to go in either direction.
>

In the absence of explicit support for the reverse relation on OneToOne
fields, can't you use the QuerySet extra() method (
http://www.djangoproject.com/documentation/db-api/#extra-select-none-where-none-params-none-tables-none)
to pull in all the various tables/columns you are interested in in a single
query?

Karen

p.s. to the Django website maintainers: pretty favicon!

--~--~-~--~~~---~--~~
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?hl=en
-~--~~~~--~~--~--~---

Re: OneToOneField direction? (was Re: QuerySet.count() inaccurate across ForeignKey relationships)

[Malcolm Tredinnick]

On Thu, 2007-11-01 at 08:53 -0400, George Vilches wrote:
> (Off-list because this mostly doesn't apply to non qs-rf people)
> 
> Thank you for the clarification on OneToOneFields and required 
> relationships.  We've been working with a very high volume legacy 
> database that we're importing the data into, and that data's not exactly 
> pristine.  We're used to related tables solving the problems with 
> non-correlated data, it sounds like we're just going to be forced to 
> clean it up, and that's fine.  Django's ORM provides a different way of 
> approaching the problem, and we just have to be willing to take full 
> advantage of that mechanism and not the previous SQL "trickery" that's 
> been used. :)
> 
> As always, your involvement in these questions is appreciated. 
> Sometimes what we expect intuitively is a bug just isn't because we're 
> taking advantage of a "non-feature" of the system.
> 
> ---
> 
> I had one other question in there directed to you, regarding 
> OneToOneFields.  In the new qs-rf, is it *possible* to make these 
> relationships bidirectional for the purposes of caching data?

Probably best to ask these questions on django-developers in future, so
that we can get a few opinions.

It might be possible when I fix up #5020. There's probably no good
reason that won't work for reverse relations. By default
select_related() won't follow reverse relations ... there are just too
many possibilities and, by and large, it would be very inefficient,
since you generally don't care about them (in the 90% case). However,
with the features of #5020 in place, you could probably specify the
reverse relations you wanted to follow and have it work. After all, User
has an implicit field called "member_set", or something like that, in
your example, so qs.select_related('member_set') might work -- not quite
sure what the reverse for a OneToOneField looks like, because I've never
used it -- I tend to stick to ForeignKey(unique=True) for a lot of those
types of things, but that's mostly an implementation detail.

I haven't finished integrating David Cramer's work there. His idea is
quite a good one, but I have to port the whole implementation across.
I've started doing it in it a branch locally, but it's still work in
progress. So I don't know if this really will work easily in practice,
but it's not beyond the realms of possibility.

> If they're not going to be currently but it is something the ORM is 
> capable of, it would be something I would work on as well as the Bit 
> class.  Here's our deal:
> 
> class User(models.Model): pass
> 
> class Member(models.Model):
>models.OneToOneField(User)
> class MemberAvatar(models.Model):
>models.OneToOneField(User)
> class MemberBadges(models.Model):
>models.OneToOneField(User)
> ...
> 
> We have 10+ of these types of tables.  The data makes sense to be 
> separated because there's a lot of large data blocks in some of these 
> columns, and we have *very large* amounts of data (100GB range on one 
> implementation of this system), so having it all in one table is 
> inappropriate.
> 
> For reporting purposes though, we would like to be able to 
> .select_related() on User, and get a cached copy of each of the OneToOne 
> relationships.  It seems reasonable by the very essence of OneToOne, but 
> I don't know if there's some limitation that would prevent that 
> following from happening.  However, when we pull 50 users, having 50*N 
> tables of extra queries when we need data from a few separate places 
> makes the whole task unappealing to use the Django ORM for.

Well, I'd be rearranging my access so that it's only N extra queries:
collect all the User objects first and then add a
filter(user_id__in=[...]). But I understand your point.

For some things, though, Django's ORM isn't going to be ideal. We aren't
trying to be SQLAlchemy. That's intentional.

Something that's been requested in the past and might be worth fleshing
out in the future (probably after queryset-refactor, though) is how to
take a result set from custom SQL and easily convert that back into a
collection of models. So that people can write their own custom SQL but
get back models in a QuerySet sort of format. Not exactly a QuerySet,
since adding extra filters probably won't work easily, but it's
interesting to play with. That's possibly even post-1.0 work, but it's
also not something that necessarily has to sit in core after the
queryset refactor lands, so I'm hoping there's some exploration that
happens there in a few months.

Regards,
Malcolm

-- 
Why be difficult when, with a little bit of effort, you could be
impossible. 
http://www.pointy-stick.com/blog/


--~--~-~--~~~---~--~~
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 

Re: OneToOneField direction? (was Re: QuerySet.count() inaccurate across ForeignKey relationships)

[George Vilches]

Alright, I guess it's not offlist.  Sorry for the extra chatter folks.

George Vilches wrote:
> (Off-list because this mostly doesn't apply to non qs-rf people)
> 
> Thank you for the clarification on OneToOneFields and required 
> relationships.  We've been working with a very high volume legacy 
> database that we're importing the data into, and that data's not exactly 
> pristine.  We're used to related tables solving the problems with 
> non-correlated data, it sounds like we're just going to be forced to 
> clean it up, and that's fine.  Django's ORM provides a different way of 
> approaching the problem, and we just have to be willing to take full 
> advantage of that mechanism and not the previous SQL "trickery" that's 
> been used. :)
> 
> As always, your involvement in these questions is appreciated. 
> Sometimes what we expect intuitively is a bug just isn't because we're 
> taking advantage of a "non-feature" of the system.
> 
> ---
> 
> I had one other question in there directed to you, regarding 
> OneToOneFields.  In the new qs-rf, is it *possible* to make these 
> relationships bidirectional for the purposes of caching data?
> 
> If they're not going to be currently but it is something the ORM is 
> capable of, it would be something I would work on as well as the Bit 
> class.  Here's our deal:
> 
> class User(models.Model): pass
> 
> class Member(models.Model):
>models.OneToOneField(User)
> class MemberAvatar(models.Model):
>models.OneToOneField(User)
> class MemberBadges(models.Model):
>models.OneToOneField(User)
> ...
> 
> We have 10+ of these types of tables.  The data makes sense to be 
> separated because there's a lot of large data blocks in some of these 
> columns, and we have *very large* amounts of data (100GB range on one 
> implementation of this system), so having it all in one table is 
> inappropriate.
> 
> For reporting purposes though, we would like to be able to 
> .select_related() on User, and get a cached copy of each of the OneToOne 
> relationships.  It seems reasonable by the very essence of OneToOne, but 
> I don't know if there's some limitation that would prevent that 
> following from happening.  However, when we pull 50 users, having 50*N 
> tables of extra queries when we need data from a few separate places 
> makes the whole task unappealing to use the Django ORM for.  I wouldn't 
> think about asking that this should be in the ORM, except that Django 
> supports reverse foreign keys so intelligently that it seems a direct 
> 1:1 correlation between rows is intuitive to go in either direction.
> 
> As always, we don't ask that you actually do anything for this if it 
> isn't done, we would just like to know if it's possible, and if you have 
> any thoughts about where the likely place to go about fixing it is in 
> qs-rf.  Or, you can tell us that we're wrong about our assumption that 
> OneToOneFields should be bidirectional, because of XXX, and we'll 
> respect your decisions.  You know what's better for Django than us, by 
> far. :)
> 
> 
> Thanks,
> George
> 
> 
> Malcolm Tredinnick wrote:
>> On Thu, 2007-11-01 at 00:43 -0400, George Vilches wrote:
>>> Karen Tracey wrote:
 On 10/31/07, *George Vilches* <[EMAIL PROTECTED] 
 > wrote:

 Or (I just saw your follow-up e-mail), is all of this a moot point 
 since
 something like this is going to be made totally invalid in the future?


 That's it.  Targeting a non-unique column in a ForeignKey field is 
 invalid.  It's invalid now, and will continue to be invalid.  The 
 difference is that it's not caught and flagged as an error now, and will 
 be when queryset-refactor gets merged into trunk. 

 If you can whittle down your real problem to an example that does not 
 include this kind of invalid relationship (you said you are actually 
 using OneToOne fields?) maybe we could make some progress on 
 understanding/solving the real issue you are facing.
>>> Here's an example that's a lot closer to home:
>>>
>>> class Person(models.Model):
>>>  name = models.CharField(max_length=100)
>>>  status = models.IntegerField()
>>>
>>> class PersonInfo(models.Model):
>>>  person = models.OneToOneField(Person)
>>>  phone = models.CharField(max_length=10)
>>>
>>>
>>>
>>>  >>> from qs.models import *
>>>  >>> Person.objects.create(name='user1', status=1)
>>> 
>>>  >>> PersonInfo.objects.create(person_id=1, phone='111')
>>> 
>>>  >>> PersonInfo.objects.create(person_id=99, phone='222')
>>> 
>>>
>>> Yes, I know.  At this point we've added a PersonInfo that doesn't map to 
>>> an existing person (id=99).
>>>
>>> If at this point you're saying, "well that's stupid", I would ask you to 
>>> point out where it says that a OneToOneField has any sort of forced 
>>> referential integrity across the relationship (it most definitely is not 
>>> generating constraints like the unique ForeignKey does).  
>> On 

OneToOneField direction? (was Re: QuerySet.count() inaccurate across ForeignKey relationships)

[George Vilches]

(Off-list because this mostly doesn't apply to non qs-rf people)

Thank you for the clarification on OneToOneFields and required 
relationships.  We've been working with a very high volume legacy 
database that we're importing the data into, and that data's not exactly 
pristine.  We're used to related tables solving the problems with 
non-correlated data, it sounds like we're just going to be forced to 
clean it up, and that's fine.  Django's ORM provides a different way of 
approaching the problem, and we just have to be willing to take full 
advantage of that mechanism and not the previous SQL "trickery" that's 
been used. :)

As always, your involvement in these questions is appreciated. 
Sometimes what we expect intuitively is a bug just isn't because we're 
taking advantage of a "non-feature" of the system.

---

I had one other question in there directed to you, regarding 
OneToOneFields.  In the new qs-rf, is it *possible* to make these 
relationships bidirectional for the purposes of caching data?

If they're not going to be currently but it is something the ORM is 
capable of, it would be something I would work on as well as the Bit 
class.  Here's our deal:

class User(models.Model): pass

class Member(models.Model):
   models.OneToOneField(User)
class MemberAvatar(models.Model):
   models.OneToOneField(User)
class MemberBadges(models.Model):
   models.OneToOneField(User)
...

We have 10+ of these types of tables.  The data makes sense to be 
separated because there's a lot of large data blocks in some of these 
columns, and we have *very large* amounts of data (100GB range on one 
implementation of this system), so having it all in one table is 
inappropriate.

For reporting purposes though, we would like to be able to 
.select_related() on User, and get a cached copy of each of the OneToOne 
relationships.  It seems reasonable by the very essence of OneToOne, but 
I don't know if there's some limitation that would prevent that 
following from happening.  However, when we pull 50 users, having 50*N 
tables of extra queries when we need data from a few separate places 
makes the whole task unappealing to use the Django ORM for.  I wouldn't 
think about asking that this should be in the ORM, except that Django 
supports reverse foreign keys so intelligently that it seems a direct 
1:1 correlation between rows is intuitive to go in either direction.

As always, we don't ask that you actually do anything for this if it 
isn't done, we would just like to know if it's possible, and if you have 
any thoughts about where the likely place to go about fixing it is in 
qs-rf.  Or, you can tell us that we're wrong about our assumption that 
OneToOneFields should be bidirectional, because of XXX, and we'll 
respect your decisions.  You know what's better for Django than us, by 
far. :)


Thanks,
George


Malcolm Tredinnick wrote:
> On Thu, 2007-11-01 at 00:43 -0400, George Vilches wrote:
>> Karen Tracey wrote:
>>> On 10/31/07, *George Vilches* <[EMAIL PROTECTED] 
>>> > wrote:
>>>
>>> Or (I just saw your follow-up e-mail), is all of this a moot point since
>>> something like this is going to be made totally invalid in the future?
>>>
>>>
>>> That's it.  Targeting a non-unique column in a ForeignKey field is 
>>> invalid.  It's invalid now, and will continue to be invalid.  The 
>>> difference is that it's not caught and flagged as an error now, and will 
>>> be when queryset-refactor gets merged into trunk. 
>>>
>>> If you can whittle down your real problem to an example that does not 
>>> include this kind of invalid relationship (you said you are actually 
>>> using OneToOne fields?) maybe we could make some progress on 
>>> understanding/solving the real issue you are facing.
>> Here's an example that's a lot closer to home:
>>
>> class Person(models.Model):
>>  name = models.CharField(max_length=100)
>>  status = models.IntegerField()
>>
>> class PersonInfo(models.Model):
>>  person = models.OneToOneField(Person)
>>  phone = models.CharField(max_length=10)
>>
>>
>>
>>  >>> from qs.models import *
>>  >>> Person.objects.create(name='user1', status=1)
>> 
>>  >>> PersonInfo.objects.create(person_id=1, phone='111')
>> 
>>  >>> PersonInfo.objects.create(person_id=99, phone='222')
>> 
>>
>> Yes, I know.  At this point we've added a PersonInfo that doesn't map to 
>> an existing person (id=99).
>>
>> If at this point you're saying, "well that's stupid", I would ask you to 
>> point out where it says that a OneToOneField has any sort of forced 
>> referential integrity across the relationship (it most definitely is not 
>> generating constraints like the unique ForeignKey does).  
> 
> On the contrary, it is asking for referential integrity at database
> table creation time. Have a look at the output of "manage.py sql
> " for that app. Unfortunately, SQLite and MySQL with the MyIsam
> storage engine won't raise complaints (since they don't enforce the
>