Please add exists subquery to queryset api

[George Ma]

I've encountered quite a few cases where I need to write complicated queries where a exists subquery makes more sense and has better performance. So far, there's no exists subquey support in queryset api. Please add it.

[Shai Berger]

Hello George,

I assume the "exists()" method on querysets does not give you what you want,
but it isn't quite clear from the above what that is.

Could you please give an example of the API you'd like to see and the SQL that
would be generated by it?

Thanks,
Shai.

[George Ma]

For simplicity, let's assume we have a model A and model B.

class A(models.Model):
    name = models.CharField(max_length=200)

class B(models.Model):
    a = models.ForeignKey(A)
    criteria = models.BooleanField(default = False)

Let's say there's a requirement to find A that doesn't have a B with a true criteria. To me, it makes more sense to use a "not exists" subquery. Right now, I have to find all the A with the reverse conditions and exclude such cases. Something like:

A.objects.exclude(pk__in = A.objects.filter(b__criteria = True))

The problem with this approach is that: first I don't know how the performance would be; second, it's not very natural from sql query's perspective.

[Curtis Maloney]

If you want to know the SQL generated for any particular queryset, you can just:

    print str(qs.query)

On 11 November 2014 11:39, George Ma <george...@gmail.com> wrote:

For simplicity, let's assume we have a model A and model B.

class A(models.Model):
    name = models.CharField(max_length=200)

class B(models.Model):
    a = models.ForeignKey(A)
    criteria = models.BooleanField(default = False)

Let's say there's a requirement to find A that doesn't have a B with a true criteria. To me, it makes more sense to use a "not exists" subquery. Right now, I have to find all the A with the reverse conditions and exclude such cases. Something like:

A.objects.exclude(pk__in = A.objects.filter(b__criteria = True))

I think you'll find this would be something like:

SELECT .... FROM myapp_a WHERE NOT pk IN (SELECT id FROM myapp_a T1 INNER JOIN myapp_b T2 ON (T1.id = T2.a_id) WHERE T2.criteria = True)

The problem with this approach is that: first I don't know how the performance would be; second, it's not very natural from sql query's perspective.

Performance should be ok, from what I can see...

Would be interested to see what the actual SQL is.

--

Curtis

[George Ma]

Yes, the actual sql is very close to this one. But I just feel this approach is like a hack. Because I always have to reverse the logic first and then reverse again. Not as natural as exists subquery.

[Curtis Maloney]

Can you provide the "natural" SQL version you'd rather see generated?

Something like:

SELECT ... FROM myapp_a WHERE NOT EXISTS (SELECT 1 FROM myapp_b T1 WHERE T1.a_id = myapp_a.id AND T1.criteria = True)

perhaps?

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To post to this group, send email to django-d...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/19f716f4-2367-492c-ac1e-a4f48c4a2c01%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[George Ma]

Yes, that's the query i'd like to have.