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

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