Re: Is this possible using the Django ORM without raw SQL?

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 post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.