Adding UNION/INTERSECT/EXCEPT to the ORM

[Florian Apolloner]

Hi,

I have a currently WIP PR at https://github.com/django/django/pull/7727

The usage is currently something like this:

qs1 = User.objects.all().values('username')
qs2 = Group.objects.all().values('name')
results = qs1.union(qs).distinct().order_by('name')[:10]

(order_by does not work though yet)

So far I have a few questions:

 * Should .union/.intersect etc return a new type of queryset or stay with the base QuerySet class (probably less code duplication)
 * We currently have a few methods which check can_filter and error out accordingly (ie you cannot filter after slicing), but as the error message in https://github.com/django/django/blob/master/django/db/models/query.py#L579 shows, this strongly relies on knowledge of the implementation of the filter. For combined querysets I basically need to limit everything aside from order by and limit/offset. Would a method like this make some sense (on the Query class):

def is_allowed(self, action):
  if self.combinatorial and action not in ('set_operation', 'order_by', 'slicing'):
    raise SomeError('Cannot use this method on an combinatorial queryset')
  elif action == 'filter' and (self.low_mark or self.high_mark):
    raise SomeError('Cannot filter after slicing')

 * set_operator in base.py feels a bit weird (https://github.com/django/django/pull/7727/files#diff-53fcf3ac0535307033e0cfabb85c5301) -- any better options?
 * How can I change the generated order_by clause to reference the columns "unqualified" (ie without table name), can I somehow just realias every column?

Cheers,
Florian

[charettes]

Hey Florian!

> How can I change the generated order_by clause to reference the columns "unqualified" (ie without table name), can I somehow just realias every column?

Now that we have F() aliasing working in iterator() I would suggest to restrict
all combined queries to have the same set on fields. e.g.

users = User.objects.values(name=F('username'))

groups = Group.objects.values('name')

results = users.union(groups).distinct().order_by('name')[:10]

This should make things easier.

Thanks for working on that!

Simon

[Florian Apolloner]

Hi Simon,

On Friday, December 23, 2016 at 3:27:46 PM UTC+1, charettes wrote:

users = User.objects.values(name=F('username'))

groups = Group.objects.values('name')

results = users.union(groups).distinct().order_by('name')[:10]

That does indeed work already, but it would be nice if it worked without .values so you can get out model objects again. Think of a union query giving you users which signed up in 2014 or 2016, surely one can easily do that via OR, but if I can get order_by to either return column numbers or realias every field from it's name to it's unqualified name (like you suggested with values, but internally in Query), that would be a massive win.

Cheers,
Florian

[Florian Apolloner]

Ok, everything seems to be working so far, I'll try adding `is_allowed` over the next few days, then I can get rid of the extra `QuerySet` class.

[Cristiano Coelho]

Is this going to be different from the pipe ( | ) and and ( & ) operators on querysets? If I'm not wrong those can already result in a union query (but not necessary, sometimes it just returns a query with an or/and condition)

[Adam Johnson]

Yes it's different, they cannot be changed due to backwards compatibility issues. They don't result in UNION in SQL, they union the filters on two querysets that are on the same exact model.

--
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-developers+unsubscribe@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/d38358ca-c97f-4bb0-a390-e38f3b4b8f6c%40googlegroups.com.

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

--

Adam

[Tim Graham]

We cannot use the name "QuerySet.except()" since except is a reserved word in Python. Do you prefer minus() (as suggested by Florian), except_() (as done by SQLAlchemy), or something else?

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 https://groups.google.com/group/django-developers.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/d38358ca-c97f-4bb0-a390-e38f3b4b8f6c%40googlegroups.com.

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

--

Adam

[Sean Bleier]

We cannot use the name "QuerySet.except()" since except is a reserved word in Python. Do you prefer minus() (as suggested by Florian), except_() (as done by SQLAlchemy), or something else?

Can I suggest using "QuerySet.difference"?   It's what python's sets use for achieving the same functionality.

To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscribe@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.

Visit this group at https://groups.google.com/group/django-developers.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/3084faa0-e097-4227-9a34-f870c8be6809%40googlegroups.com.

[charettes]

+1 to QuerySet.difference()

[Florian Apolloner]

Jupp, sounds good to me too -- will see that I get the PR updated this afternoon