Django custom SQL to return QuerySet where each object has additional properties

Question

Let's say I have the following objects:

squirrel_table 
  - name
  - country_of_origin
  - id

nut_table
  - id
  - squirrel_who_owns_me[fk to squirrel]

I want to retrieve a list of all squirrels in a particular country. The returned squirrel objects can be in a QuerySet, but do not need to be. A list will suffice. Each squirrel will have an additional property call nut_count. The SQL for this would be something like what follows (please note I use the subquery in order to not enumerate all of squirrel's columns, since in reality there will be many) (I use PostgreSQL):

select sq.*,
       nut_counts.nut_count
  from squirrel_table sq,
      (select sq2.id as squirrel_id,
              count(nuts) as nut_count
         from squirrel_table sq2,
              nut_table nuts
        where nuts.squirrel_who_owns_me = sq2.id
          and sq.country_of_origin = 'USA'
        group by sq2.id) as nut_counts
 where sq.id = nut_counts.squirrel_id

Is there a way to execute the SQL, take the cursor it generates, and turn it into a list of squirrel objects, and add the nut_count to each squirrel?

Answer 1

You probably want to read through the documentation for the "extra()" method, which includes an example of a similar "select something else and append it onto each object" situation.

Answer 2

If you need to execute SQL, use raw(). if you add in a pk "select table.id as pk, table.* ...", then it will act like the QuerySet that you may be used to.

sql = 'select sq.id as pk, sq.* ... %s'
squirreled = squirrel_table.objects.raw(sql, ["arg1"])