[FIXED] Calculate intersection of two Django queries: one aggregate, another - simple

Issue

I have a model VariantTag that stores the ids of another model called SavedVariant. The former has another variant_tag_type_id that is pointing to its relative model type VariantTagType. Now I am trying to get all SavedVariant ids which have only one variant_tag_type.name = 'Review' tag. To make things clearer here is what I am trying to do in Django:

    # Variants with just only one tag present                                                                          
    single_variant_ids = VariantTag.objects.values_list('saved_variant_id', flat=True) \                               
            .annotate(id_count=Count('saved_variant_id')).filter(id_count=1)                                           
    # All variants that have 'Review' tag                                                                              
    review_all_variant_ids = VariantTag.objects.filter(variant_tag_type__name='Review') \                              
            .values_list('saved_variant_id', flat=True)                                                                
    # Intersection of the previous two queries                                                                         
    review_variant_ids = single_variant_ids.intersection(review_all_variant_ids)

And this is not working giving me an error:

ProgrammingError: each INTERSECT query must have the same number of columns LINE 1: ...nttag"."saved_variant_id") = 1) INTERSECT (SELECT "seqr_vari...

How could I write such a query in Django?

Update

I used the advice of Omar and was able to eliminate the error by rewriting the second query like that:

review_all_variant_ids = VariantTag.objects.filter(variant_tag_type__name='Review') \                              
                .values_list('saved_variant_id', flat=True).annotate(val=Value(0, output_field=IntegerField()))  

However, the intersection does not calculate intersection correctly instead just returning the empty QuerySet. I checked both QuerySets converting them to python lists and printing them out and here is what I see:

single_variant_ids: [46, 28, 38, 30, 33, 29, 47, 31, 44]
review_all_variant_ids: [22, 36, 46, 47]
review_variant_ids: []

As you can see the intersection result should not be empty but should be a QuerySet with the values: 46 and 47. I also tried to just write intersection like that:

single_variant_ids & review_all_variant_ids 

But it is giving an error:

TypeError: Merging 'QuerySet' classes must involve the same values in each case.

Update

I changed the name of the empty column of the second QuerySet:

review_all_variant_ids = VariantTag.objects.filter(variant_tag_type__name='Review') \                              
                .values_list('saved_variant_id', flat=True).annotate(id_count=Value(0, output_field=IntegerField()))

After which the following worked:

review_variant_ids = single_variant_ids & review_all_variant_ids

But the result is wrong:

[22, 36, 46, 47]

So, the intersection is performed in the wrong way here, not like I need. Of course, the easiest is just to convert both QuerySets to python sets and calculate their intersection but I want to avoid querying the database until the very last point.

Solution

Initially, you had annotated the first query with id_count which means it will be a column in the result of the query, this column doesn't exist in the second query, you might just want to annotate it there as Value(0) to keep the columns consistent when intersecting.

Once you've done that, you can now try your initial attempt at intersection or you can try this filter:

single_variant_ids.filter(saved_variant_id__in=review_all_variant_ids.values_list('saved_variant_id'))

All the best

Answered By - Omar