Is this possible using the Django ORM without raw SQL?

[sbrandt]

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.

Thanks,

Sebastian

[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

[sbrandt]

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.

[akaariai]

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

[sbrandt]

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! :)