Reducing queries for manytomany models in django

Question

EDIT: It turns out the real question is - how do I get select_related to follow the m2m relationships I have defined? Those are the ones that are taxing my system. Any ideas?

I have two classes for my django app. The first (Item class) describes an item along with some functions that return information about the item. The second class (Itemlist class) takes a list of these items and then does some processing on them to return different values. The problem I'm having is that returning a list of items from Itemlist is taking a ton of queries, and I'm not sure where they're coming from.

class Item(models.Model):

# for archiving purposes
archive_id  = models.IntegerField()
users       = models.ManyToManyField(User, through='User_item_rel',
                                     related_name='users_set')

# for many to one relationship (tags)
tag         = models.ForeignKey(Tag)
sub_tag     = models.CharField(default='',max_length=40)

name        = models.CharField(max_length=40)
purch_date  = models.DateField(default=datetime.datetime.now())
date_edited = models.DateTimeField(auto_now_add=True)
price       = models.DecimalField(max_digits=6, decimal_places=2)
buyer       = models.ManyToManyField(User, through='Buyer_item_rel',
                                     related_name='buyers_set')
comments    = models.CharField(default='',max_length=400)
house_id    = models.IntegerField()

class Meta:
    ordering = ['-purch_date']

def shortDisplayBuyers(self):
    if len(self.buyer_item_rel_set.all()) != 1:
        return "multiple buyers"
    else:
        return self.buyer_item_rel_set.all()[0].buyer.name
def listBuyers(self):
    return self.buyer_item_rel_set.all()

def listUsers(self):
    return self.user_item_rel_set.all()

def tag_name(self):
    return self.tag

def sub_tag_name(self):
    return self.sub_tag

def __unicode__(self):
    return self.name

and the second class:

class Item_list:

def __init__(self, list = None, house_id = None, user_id = None,
             archive_id = None, houseMode = 0):
    self.list = list
    self.house_id = house_id
    self.uid = int(user_id)
    self.archive_id = archive_id
    self.gen_balancing_transactions()
    self.houseMode = houseMode

def ret_list(self):
    return self.list

So after I construct Itemlist with a large list of items, Itemlist.ret_list() takes up to 800 queries for 25 items. What can I do to fix this?

Answer 1

Try using select_related

As per a question I asked here

Answer 2

Dan is right in telling you to use select_related.

select_related can be read about here.

What it does is return in the same query data for the main object in your queryset and the model or fields specified in the select_related clause.

So, instead of a query like:

select * from item

followed by several queries like this every time you access one of the item_list objects:

select * from item_list where item_id = <one of the items for the query above>

the ORM will generate a query like:

select item.*, item_list.* 
  from item a join item_list b
 where item a.id = b.item_id

In other words: it will hit the database once for all the data.