Yes, it sure isn't a one-to-one relationship technically, but maybe "weak"
one-to-one relationships using a special type-field are an interesting
approach. On the other hand, I don't know how much use cases there are for
such a relation.
In all cases, this would need a change to the database backend using outer
joins, doesn't it? So my problem isn't likely to be solved more elegant in
the near future. Thanks for all! :)
Am Samstag, 28. Juli 2012 23:15:37 UTC+2 schrieb akaariai:
>
> On 28 heinä, 15:39, sbrandt <s.brandt.ber...@googlemail.com> wrote:
> > > On 28 heinä, 12:20, sbrandt <s.brandt.ber...@googlemail.com> wrote:
> > > > Hello,
> >
> > > > for the first time ever how to compile a query with the Django ORM
> :/
> >
> > > > There are two Models: The second model has a foreign key to the
> first
> > > model
> > > > and a type, where the combination of "first model and second model's
> > > type"
> > > > is unique. Let's say type 1 are dogs, type 2 are cats and every
> model1
> > > can
> > > > only have 0 or 1 dogs and cats. I know that it could have been
> > > implemented
> > > > as two seperate tables without types and one-to-ones, but I need to
> > > query
> > > > the whole model2 often and it's implemented in this way since years.
> >
> > > > This is not a problem for single model1 objects, since it's just a
> > > second
> > > > objects.get(...)-call as a lazy property in the model1.
> >
> > > > But now I need a query for *all* model1-objects with two additional
> > > > columns: Has this model1 a cat, has this model1 a dog?
> >
> > > > Here's some code (Just pseudocode - doesn't work! My actual models
> would
> > > be
> > > > too complicated):
> >
> > > > class Model1(Model):
> > > > name = CharField(...)
> >
> > > > m2types = (cats, dogs)
> >
> > > > class Model2(Model)
> > > > m1 = ForeignKey(Model1)
> > > > type = SmallIntegerField(..., choices=m2types)
> > > > class Meta:
> > > > unique_together = ('m1', 'type')
> >
> > > > In SQL I would do it with left outer joins:
> >
> > > > SELECT m1.id, m1.name, c.id, d.id
> > > > FROM model1 AS m1
> > > > LEFT OUTER JOIN model2 AS c ON c.m1_id = m1.id AND c.type = 1
> > > > LEFT OUTER JOIN model2 AS d ON d.m1_id = m1.id AND d.type = 2;
> >
> > > > So, back to my questsion: Is this possible with the Django ORM
> without
> > > > using raw SQL?
> >
> > > > Note: Speed is not important. Since it is a cronjob being done half
> a
> > > year
> > > > and my models are just hundrets, iterating over every model1 and
> using
> > > the
> > > > lazy property cat and dog would be okay, and also using raw SQL
> would be
> > > > okay since I'm tied to PostgreSQL. I'm explicitly searching for an
> > > elegant
> > > > solution with the Django ORM.
> >
> > > Hmmh, so you want to fetch every object, and "annotate" the
> > > information about having a dog or a cat in the original model. I don't
> > > think that can be done, although there might be some trick for this.
> >
> > > What you could do is use the prefetch_related() method, and then do
> > > the annotation in Python code.
> >
> > > Something like this:
> > > objs = Model1.objects.prefetch_related('model2_set')
> >
> > > And in model1 you could have two properties, has_cat and has_dog
> >
> > > class Model1:
> > > ...
> > > def _has_dog(self):
> > > return any(obj for obj in self.model2_set if obj.type == DOG)
> > > has_dog = property(_has_dog)
> > > ...
> >
> > > While this isn't elegant it gets the work done... If you need to
> > > filter or do something else in the ORM with the has_dog information,
> > > you will not be able to do this using the above idea.
> >
> > > Django's ORM can be somewhat hard to use when working with reverse
> > > foreign key data. There is room to improve the annotation mechanism of
> > > Django. I hope the situation will improve... The prefetch_related
> > > machinery has helped with many situations already.
> >
> > > - Anssi
> >
> > Okay, so I'll take a closer look at prefetch_related. I didn't know one
> can
> > prefetch the model_set.
> >
> > If I would have splitted up into one Dog and one Cat table using
> one-to-one
> > to Model1, I could have just used select_related on the backwards
> relation.
> > But I don't see any way to tell Django this is in fact a one-to-one
> > relationship.
>
> Well, it is not a one-to-one relationship, without the added filter on
> type.
>
> I really wish we had something like this in Django:
> qs = Model1.objects.annotate(dog=ModelAnnotation('model2_set',
> Q(model2_set__type='dog'))
>
> This would do something similar to select_related - every object is
> annotated with Model2 instances. You could do further operations to
> the annotated model:
> qs.order_by('dog__type')
>
> The query generated would be something like this:
>
> select ...
> from model1
> left join model2 on model2.model1_id = model1.id and model2.type =
> 'dog'
> order by model2.type;
>
> I do think the above is possible to achieve, but there is some more
> work to be done...
>
> - Anssi
>
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/django-users/-/eLxrqZepq4wJ.
To post to this group, send email to django-users@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.
