Django QuerySet difference method not working

Question

Im trying to get the difference between two QuerySets and it is important to me that the answer of this difference be a QuerySet. So, the natural solution is to use the difference method of the Django queryset. However, when trying to do that Im getting the following error:

NotSupportedError: difference is not supported on this database backend.

Here what Im trying to do:

In [4]: type(small_qs)
Out[4]: django.db.models.query.QuerySet

In [5]: type(bigger_qs)
Out[5]: django.db.models.query.QuerySet

In [6]: bigger_qs.difference(small_qs)

Important: Two QuerySets are from the same model.

Other information that can be usefull:

• Using docker (database and django)
• The database backend is MariaDB (MySQL)
• Django version is 2.0.6
• MariaDB version is 10.3.8

Here the complete output:

Out[6]: ---------------------------------------------------------------- 
NotSupportedError                         Traceback (most recent call 
last)
/usr/local/lib/python3.6/site-packages/IPython/core/formatters.py in 
__call__(self, obj)
700                 type_pprinters=self.type_printers,
701                 deferred_pprinters=self.deferred_printers)
702             printer.pretty(obj)
703             printer.flush()
704             return stream.getvalue()

/usr/local/lib/python3.6/site-packages/IPython/lib/pretty.py in 
pretty(self, obj)
398                         if cls is not object \
399                                 and 
callable(cls.__dict__.get('__repr__')):
400                             return _repr_pprint(obj, self, cycle)
401
402             return _default_pprint(obj, self, cycle)

/usr/local/lib/python3.6/site-packages/IPython/lib/pretty.py in 
_repr_pprint(obj, p, cycle)
693     """A pprint that just redirects to the normal repr function."""
694     # Find newlines and replace them with p.break_()
695     output = repr(obj)
696     for idx,output_line in enumerate(output.splitlines()):
697         if idx:

/usr/local/lib/python3.6/site-packages/django/db/models/query.py in         
__repr__(self)
246
247     def __repr__(self):
248         data = list(self[:REPR_OUTPUT_SIZE + 1])
249         if len(data) > REPR_OUTPUT_SIZE:
250             data[-1] = "...(remaining elements truncated)..."

/usr/local/lib/python3.6/site-packages/django/db/models/query.py in 
__iter__(self)
270                - Responsible for turning the rows into model 
objects.
271         """
272         self._fetch_all()
273         return iter(self._result_cache)
274

/usr/local/lib/python3.6/site-packages/django/db/models/query.py in 
_fetch_all(self)
1177     def _fetch_all(self):
1178         if self._result_cache is None:
1179             self._result_cache = list(self._iterable_class(self))
1180         if self._prefetch_related_lookups and not 
self._prefetch_done:
1181             self._prefetch_related_objects()

/usr/local/lib/python3.6/site-packages/django/db/models/query.py in 
__iter__(self)
51         # Execute the query. This will also fill compiler.select, 
klass_info,
52         # and annotations.
53         results = 
compiler.execute_sql(chunked_fetch=self.chunked_fetch, 
chunk_size=self.chunk_size)
54         select, klass_info, annotation_col_map = (compiler.select, 
compiler.klass_info,
55                                                   
compiler.annotation_col_map)

/usr/local/lib/python3.6/site-packages/django/db/models/sql/compiler.py 
in execute_sql(self, result_type, chunked_fetch, chunk_size)
1053             result_type = NO_RESULTS
1054         try:
1055             sql, params = self.as_sql()
1056             if not sql:
1057                 raise EmptyResultSet

/usr/local/lib/python3.6/site-packages/django/db/models/sql/compiler.py 
in as_sql(self, with_limits, with_col_aliases)
452             if combinator:
453                 if not getattr(features, 
'supports_select_{}'.format(combinator)):
454                     raise NotSupportedError('{} is not supported 
on this database backend.'.format(combinator))
455                 result, params = self.get_combinator_sql(combinator, 
self.query.combinator_all)
456             else:

NotSupportedError: difference is not supported on this database backend.

Please, if need some more data, let me know.

Answer 1

According to this ticket django doesn't officially support MariaDB. Most of the functionality is apparently there, but there seems to be some difficulty in determining the version or distinguishing between MySQL and MariaDB. I'm guessing that the error you are seeing has to do with that.

That said, depending on what you are trying to do, difference may not actually be "the natural solution" to your problem. If (as I suspect) your two querysets are actually on the same model, the simplest way to do what you want would be to use exclude. Something along these lines:

bigger_qs.exclude(id__in=smaller_qs)

This does not produce the same SQL (difference uses EXCEPT while exclude uses WHERE NOT), it should produce the same set of results (potentially in a different order).

Answer 2

This code may help:

check = Qs1.objects.all()
prg=[]
[prg.append(x.ref) for x in check]
difference = (Qs2.objects.exclude(ref__in=prg).values())