Combine two Django Querysets based on common field

Question

I have two Querysets (actually, list of dicts) like:

q1 = M1.objects.filter(id=pk).values('p_id', 'q1_quantity')
# q1: <Queryset[{'p_id': 2, 'q1_quantity': 4}, {'p_id': 3, 'q1_quantity': 5}]>

q2 = M2.objects.filter(p_id__in=[q1[x]['p_id'] for x in range(len(q1))]).values('p_id', 'q2_quantity')
# q2: <Queryset[{'p_id': 2, 'q2_quantity': 2}, {'p_id': 2, 'q2_quantity': 5}, {'p_id': 3, 'q2_quantity': 1}, {'p_id': 3, 'q2_quantity': 7}]>

q1 has distinct key:value pairs, while q2 has repeated keys.

1) I want to sum all the values of q2 by common p_id, such that q2 becomes:

# q2: <Queryset[{'p_id': 2, 'q2_quantity': 7}, {'p_id': 3, 'q2_quantity': 8}]>

2) Then, merge q1 and q2 into q3, based on common p_id, like:

q3 = ?
# q3: <Queryset[{'p_id': 2, 'q1_quantity': 4, 'q2_quantity': 7}, {'p_id': 3, 'q1_quantity': 5, 'q2_quantity': 8}]>

I have looked into union(). But don't know how to go about summing the queryset (q2) and then merging it with q1.

Can someone please help me?

score 0 · Answer 1 · answered May 14 '20 at 16:36

The problem is that you're implementing inefficient models, having 2 separate models with repeated fields will force you to make 2 queries. You may want to consider having them all in one model, or the M2 model extends M1.

models.py

class M(models.Model):
    p_id = #Your Field...
    q1_quantity = #Your Field...
    q2_quantity = #Your Field...

then on your views.py

q = M.objects.filter(id=pk).values('p_id', 'q1_quantity', 'q2_quantity')

Potential Issue: In the code you posted, the commented section shows a queryset of more than 1 object and pk as primary key should be unique and therefore should return a unique object queryset.

M1 and M2 are models of two different apps. I require them to be separate, but at the same time need their quantities to be displayed together. M1 is basically a component model and M2 is process model. p_id is basically "product" field (FK field) common to both the models. I require both the component quantity and process quantity together in a view. — hobo, May 14 '20 at 21:25

score 0 · Accepted Answer · answered May 15 '20 at 16:23

1) I want to sum all the values of q2 by common p_id, such that q2 becomes:

# q2: <Queryset[{'p_id': 2, 'q2_quantity': 7}, {'p_id': 3, 'q2_quantity': 8}]>

Used itertools.combinations:

from itertools import combinations

compare = []
for a, b in combinations(q2, 2):
    if a['p_id'] == b ['p_id']:
        a['q2_quantity'] += b['q2_quantity']
        if len(compare) <= 0:
            compare.append(a)
        else:
            [compare[d]['q2_quantity'] for d in range(len(compare)) if a['p_id'] == compare[d]['p_id']]

    else:
        if len(compare) <= 0:
            compare.append(a)
            compare.append(b)
        else:
            if any([a['p_id'] == compare[d]['p_id'] for d in range(len(compare))]):
                pass
            else:
                compare.append(a)
            if any([b['p_id'] == compare[d]['p_id'] for d in range(len(compare))]):
                pass
            else:
                compare.append(b)

2) Then, merge q1 and q2 into q3, based on common p_id, like:

q3 = ?
# q3: <Queryset[{'p_id': 2, 'q1_quantity': 4, 'q2_quantity': 7}, {'p_id': 3, 'q1_quantity': 5, 'q2_quantity': 8}]>

As per this SO post:

from collections import defaultdict
from itertools import chain

collector = defaultdict(dict)

for collectible in chain(cp, compare):
    collector[collectible['p_id']].update(collectible.items())

products = list(collector.values())

Combine two Django Querysets based on common field

2 Answers2