Need help using data from a Select_related() queryset

Question

I'm working on a parts database, where every part number can also be an assembly, meaning it is composed of any number of other parts (and the loop can go on, subparts being made of even more parts, etc). So there are two database tables, one for part information, and the other for relationship information- a part number linked to its "subpart" number. Keep in mind that "assemblies", "parts", and "subparts" are all ultimately just "parts (kind of confusing, but it allows for a more DRY and versatile database).

I'm currently using the select_related call to follow the ForeignKeys used in my model. However, because my query may return more than just a single result (if multiple subparts), I can't use the "get" lookup and I'm using "filter" instead. So- I can't follow the examples shown in the documentation which are all based off a get query.

The select_related query seems to be grabbing what I intend for it to (based on the raw SQL queries shown by DjangoDebugToolbar). But, I don't know how to call it! What is the correct syntax or method for displaying values from the related tables? How can I loop over every instance within the returned queryset? The snippet below from the template should most effectively show what I'm trying to obtain as a result. Thanks.

#----------------
#MODEL SNIPPET
#----------------
class Part(models.Model):
    ISC_CHOICES = ( #intentionaly removed for this question 
    )
    part_no = models.CharField(max_length=15, primary_key=True)
    description = models.CharField(max_length=40, blank=True, null=True)
    isc = models.CharField(max_length=2, choices=ISC_CHOICES)
    rev = models.CharField(max_length=2, blank=True, null=True)

#this table relates subparts to the part model above- basically is a manual many-to-many field
class PartAssembly(models.Model):
    id = models.AutoField(primary_key=True)
    part_no = models.ForeignKey(Part, related_name="partno_set")
    subpart = models.ForeignKey(Part, related_name="subpart_set")
    qty = models.IntegerField(max_length=3)
    item_no = models.IntegerField(max_length=3)


#----------------
#VIEW SNIPPET
#----------------
def assembly_details(request, assembly_no): #assembly_no passed through URL
    context_instance=RequestContext(request)
    subpart_list = PartAssembly.objects.filter(part_no=assembly_no).select_related()
    return render_to_response('assembly_details.html', locals(), context_instance,)


#-------------------
# TEMPLATE SNIPPET
#-------------------
{% for partassembly in subpart_list %} 
# obviously, this  loop doesnt return anything for my part.foo variables below
# it does work for the partassembly.bar
        <tr>
            <td>{{ partassembly.item_no }}</td> #EDIT: comments are correct
            <td>{{ partassembly.subpart }}</td> #partassembly.subpart.part_no
            <td>{{ part.description }}</td> #partassembly.subpart.description
            <td>{{ part.rev }}</td>     #partassembly.subpart.rev   
            <td>{{ partassembly.qty }}</td>
            <td>{{ part.isc }}</td>         #partassembly.subpart.isc
        </tr>

Thanks for any help

Answer 1

I'm not sure where your trouble is exactly. Remember that select_related() does not in any way change the object access for related instances - all it does is pre-cache them. So you refer to partassembly.part_no.rev and so on, exactly as if you were not using select_related.

Answer 2

All select_related does, is eagerly fetch the fields declared as ForeignKey within your model. It attempts to avoid extra database calls, it doesn't magically give you access to extra fields.

In your example, that means that accessing partassembly.subpart will not result in an extra database select, since it was eagerly fetched with the PartAssembly.objects.filter() call.

Your data model seems to be incorrect, but we'll get to that in a minute. First, I'll show you how to access all the bits and pieces with your current data model.

{% for partassembly in subpart_list %} 
        <tr>
            <td>{{ partassembly.item_no }}</td>
            {% for subpart in partassembly.subpart.subpart_set %} # database hit
                 <td>{{ subpart.subpart }}</td>
                 <td>{{ subpart.subpart.description }}</td> # database hit
                 <td>{{ subpart.subpart.rev }}</td>         
                 <td>{{ subpart.qty }}</td>
                 <td>{{ subpart.subpart.isc }}</td>
            {% endfor %}
        </tr>

Unfortunately, you have no way of knowing how far you need to recurse. You can access the part within the original PartAssembly, and you can reach the set of PartAssemblys from that Part, but there is no easy way of reaching the PartAssembly set for all Parts within the first PartAssembly. Wow, that was a mouthful!

Now, onto your data model.

Say you have a Part called a "3mm screw". That sounds like it could be used for a number of different Assemblys (I'm intentionally not using the 'ies' plural form). So you have an Assembly called a Desk, and an Assembly called a Chair. Each uses many of these 3mm screws. You want to describe how to build a Desk.

 desk = PartAssembly.objects.filter(id=assemblyid)
 part = desk.subpart # lets pretend this returns a 3mm screw
 subassemblies = part.subpart_set.all()
 for a in subassemblies:
     a.subpart.description # could be a Chair, or a Desk, or anything really!

This happens because a single instance of a 3mm Screw (or ANY part), is shared between all assemblies. You aren't truly replicating a ManyToMany table at all. Your datamodel is saying that a single Part can be used in many Assemblys.

What I think you really want to say, is that an Assembly can be a part of another Assembly. Each Assembly as a number of Parts associated with its construction.

class Assembly(models.Model):
    parent = models.ForeignKey('self', null=True, blank=True, related_name='children')
    parts = models.ManyToManyField(Part)
    name = models.CharField(max_length=..)

Now, when you want to build a chair, you can do something like:

assembly = Assembly.objects.get(name='Chair')
children = assembly.children.all()
print assembly
for part in assembly.parts:
    print part
# now you iterate over the tree of children, printing their parts as you go

So, your Assembly model has now transformed into a Tree structure of other Assemblys, each containing their own set of Parts. Now that you can recognise this is a Tree structure, you can investigate how to represent this structure in a database within Django.

Luckily, there exists a library that does exactly this. django-mptt exists to help you represent tree structures. It gives you methods for iterating over the entire tree, and describing each tree in your templates.

I know I've probably helped cause you more work than you thought would be required, but I think this will truly help you.

Best of luck.