join tables with django

Question

queryObj = Rating.objects.select_related(
    'Candidate','State','RatingCandidate','Sig','Office','OfficeCandidate').get(
        rating_id = ratingId, 
        ratingcandidate__rating = ratingId,
        ratingcandidate__rating_candidate_id = \
             officecandidate__office_candidate_id)

This line gives me an error. I'm trying to get many different tables that are linked by primary keys and regular ids. The last selection is the problem:

ratingcandidate__rating_candidate_id = officecandidate__office_candidate_id.  

I need to skip around to get all the data.

Answer 1

I'm trying to get many different tables that are linked by primary keys and regular ids.

Don't try to "join" tables. This isn't SQL.

You have to do multiple gets to get data from many different tables.

Don't worry about select_related until you can prove that you have a bottle-neck.

Just do the various GETs from the various classes as needed.

Let's focus on Candidate and Rating.

class Rating( Model ):
    ...

class Candidate( Model ):
    rating = Models.ForeignKey( Rating )

Do this.

r = Rating.objects.get( id=rating_id )
c = r.candidate_set().all()

This will get the rating and all the candidates that have that rating. This is -- in effect -- what a SQL join is: it's two fetches. In the Django ORM, just write the two fetches as simply as possible. Let Django (and your database) cache things for you.

To display elements of multiple tables in a single row on a template form, you do this.

In the view:

r = Rating.objects.get( id=rating_id )
return render_to_response( some_form, { 'rating':r } )

In the template:

Rating: {{rating}}.  Candidates: {% for c in rating.candidate_set.all %} {{c}} {%endfor%}

Etc.

You simply "navigate" among your objects in your template to display the requested information.

Answer 2

You can't use the double-underscore syntax on its own on the right-hand side of the expression. If you need to reference field names on the right-hand side, use the F() function:

ratingcandidate__rating_candidate_id = F('officecandidate__office_candidate_id')