CODEWITHSUNDEEP
pythondjangoobjectdjango-queryset
Andrew
Andrew

Reputation: 12432

Combine and manipulate querysets based on if object in one is not in another

For some reason I cannot figure out the best way to do this.

I can merge them, get the difference, etc - but can't figure out how to merge both query sets based on values, and then assign a different value after they're merged.

I have two QuerySets:

query_set_a = Reports.objects.filter(
        agent=report.profile.agent_code, 
        product='product_a', 
        date__range=(report_range_start, report_range_end),
        prem_submitted__gt=0).order_by('date')

query_set_b = Reports.objects.filter(
        agent=report.profile.agent_code, 
        product='product_b', 
        date__range=(report_range_start, report_range_end),
        prem_submitted__gt=0).order_by('date')

If you consider the following:

{% for each in query_set_a %} 

[{{ each.date|date:"m-d" }} - {{ each.activated }}],

{% endfor %}

{% for each in query_set_b %} 

[{{ each.date|date:"m-d" }} - {{ each.activated }}],

{% endfor %}

Which outputs correctly:

query_set_1 output
[08-01 - 2], [08-02 - 2], [08-03 - 1], [08-06 - 1], [08-07 - 1], [08-10 - 13], [08-13 - 2]
query_set_2 output
[08-01 - 21], [08-02 - 23], [08-03 - 18], [08-06 - 17], [08-07 - 5], [08-09 - 2], [08-10 - 30], [08-13 - 13], [08-15 - 4],

How would I take values that are in query_set_2 output and add them to query_set_1, but set query_set_1.activated to null? Such that query_set_1 would look like:

[08-01 - 2], [08-02 - 2], [08-03 - 1], [08-06 - 1], [08-07 - 1], [08-09 - null], [08-10 - 13], [08-13 - 2], [08-15 - null]

Upvotes: 0

Views: 47

Answers (1)

vorujack
vorujack

Reputation: 1950

you can use case when like this.

Reports.objects.filter(
    agent=report.profile.agent_code, 
    product__in=['product_a', 'product_b'], 
    date__range=(report_range_start, report_range_end),
    prem_submitted__gt=0).annotate(
         new_activate=models.Case(
               models.When(product='product_a', then='activated')
               default=None
               output_field=models.IntegerField()
         )
    ).order_by('date')

Upvotes: 1

Related Questions