5

I'm trying to do a union of two querysets with shared fields.

This works:

fields = ['id', 'date_trans', 'total', 'allocated', 'balance']
qs1 = Order.objects.values_list(*fields)
qs2 = OnAccount.objects.values_list(*fields)
return qs1.union(qs2)

The Order model has a CharField called num_invoice which I'd like to include as a field in the union. This field doesn't exist in the OnAccount model so in order to include it in the values_list() I'm using an annotation.

The annotation works fine but the union causes an error:

django.db.utils.ProgrammingError: UNION types character varying and date cannot be matched

Here's my annotation and the union that does not work:

from django.db.models import CharField, Values as V
from django.db.models.functions import Concat

fields = ['id', 'num_invoice', 'date_trans', 'total', 'allocated', 'balance']
qs1 = Order.objects.values_list(*fields)
qs2 = (
    OnAccount.objects
    .annotate(num_invoice=Concat(V('OA-'), 'id', output_field=CharField()))
    .values_list(*fields)
)
return qs1.union(qs2)

Updated Info

>>> qs1.model._meta.get_field('num_invoice')
<django.db.models.fields.CharField: num_invoice>

>>> qs1.model._meta.get_field('date_trans')
<django.db.models.fields.DateField: date_trans>

>>> qs2.model._meta.get_field('date_trans')
<django.db.models.fields.DateField: date_trans>
bdoubleu
  • 5,568
  • 2
  • 20
  • 53
  • 1
    The error says that one of the values is a `date` in one model, and a `CharField` in the other. – Willem Van Onsem Aug 26 '19 at 12:46
  • @WillemVanOnsem any ideas why that's happening when the output field is specified as `CharField` on the annotation and I'm not modifying any date fields? Your comment gave me the idea to try it with an annotation on both models (`invoice=F('num_invoice')` & `invoice=Concat(...)`) and the union works now. – bdoubleu Aug 26 '19 at 13:15
  • this one can help you : https://stackoverflow.com/questions/31520629/union-type-mismatch – Valentin Garreau Aug 26 '19 at 13:19

0 Answers0