Django query get common items based on attribute -

i have model follows:

class item(models.model):     vendor_choices = (         ('a', 'a'),         ('b', 'b')     )      vendor = models.charfield(max_length=16, choices=vendor_choices)     name = models.charfield(max_length=255)     price = models.decimalfield(max_digits=6, decimal_places=2) 

now have 2 data sources, items vendor , items vendor b.

in cases vendor may not have same items vendor b, vendor has 30 items , vendor b has 442 items, out of 6 items common. items common defined items have exact same name.

i need find difference in prices of items common vendor , vendor b items, meaning items have same name in vendor , vendor b. have large no. of items may go upto 10k items per vendor, efficient way of doing required?

i think should work:

vendor_a_items = item.objects.filter(vendor='a') vendor_b_items = item.objects.filter(vendor='b')  common_items = vendor_a_items.filter(                        name__in=vendor_b_items.values_list('name', flat=true)) 

update: find price difference can loop on found common items:

for a_item in common_items:     b_item = vendor_b_items.get(name=a_item.name)     print u'%s: %s' % (a_item.name, a_item.price - b_item.price) 

this adds db hit each found item if have small number of common items solution work fine. larger intersection can load prices vendor_b_items in 1 query. use code instead of previous snippet.

common_items_d = {item.name: item item in common_items} b_item in vendor_b_items.filter(name__in=common_items_d.keys()):     print u'%s: %s' % (b_item.name,                       common_items_d[b_item.name].price - b_item.price)