Am Samstag, 28. Juli 2012 14:04:29 UTC+2 schrieb akaariai:
>
> 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.
--
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/-/KEAGQ2JQsSMJ.
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.
