expanding the SQL query inside managers in Django models?

Question

Here is the code from django docs that explains the use of managers.

class PollManager(models.Manager):
    def with_counts(self):
        from django.db import connection
        cursor = connection.cursor()
        cursor.execute("""
            SELECT p.id, p.question, p.poll_date, COUNT(*)
            FROM polls_opinionpoll p, polls_response r
            WHERE p.id = r.poll_id
            GROUP BY p.id, p.question, p.poll_date
            ORDER BY p.poll_date DESC""")
        result_list = []
        for row in cursor.fetchall():
            p = self.model(id=row[0], question=row[1], poll_date=row[2])
            p.num_responses = row[3]
            result_list.append(p)
        return result_list

class OpinionPoll(models.Model):
    question = models.CharField(max_length=200)
    poll_date = models.DateField()
    objects = PollManager()

class Response(models.Model):
    poll = models.ForeignKey(OpinionPoll)
    person_name = models.CharField(max_length=50)
    response = models.TextField()

I have two questions based on this code:

1) where is r.poll_id coming from? I understand Response has foreignKey relationship to OpinionPoll. In order to JOIN OpinionPoll table with Response table, I need to join on their id. HOwever to access the poll id in Response, I would do r.poll.id. Is the syntax, r.poll_id, a MySQL syntax.

why GROUP BY p.id, p.question, p.poll_date? why GROUP BY p.id alone is not sufficient?

2) Is it possible to turn the above raw SQL query into a django ORM query?If so how would that look like?

I am not a SQL guy. so bear with me, if this sounds stupid

EDIT:

If I want to create OpinionPoll and Response tables outside of Django, how will SQL statment for create look like?

In the Django shell, when I run

python manage.py sqlall appname

I get the following:

BEGIN;

CREATE TABLE "myapp_opinionpoll" (
    "id" integer NOT NULL PRIMARY KEY,
    "question" varchar(200) NOT NULL,
    "poll_date" date NOT NULL
)
;
CREATE TABLE "myapp_response" (
    "id" integer NOT NULL PRIMARY KEY,
    "poll_id" integer NOT NULL REFERENCES "myapp_opinionpoll" ("id"),
    "person_name" varchar(50) NOT NULL,
    "response" text NOT NULL
)
;
CREATE INDEX "larb_response_70f78e6b" ON "myapp_response" ("poll_id");

COMMIT;

I see something like REFERENCES "myapp_opinionpoll" and CREATE INDEXabove. I am not sure if this is how in SQL it is done?

Answer 1

[1] Django model will create foreign keys like fieldname_id as the field in mysql. So you see the field poll = models.ForeignKey(OpinionPoll) creates this field.

About GROUP BY, because these fields are exactly what selected, except for the aggregate function, grouping them exactly can make them distinct.

[2] Try this, I didn't debug, but may helps:

from django.db.models import Count
OpinionPoll.objects.annotate(num_responses=Count('response'))

For more about aggregation, see the docs: https://docs.djangoproject.com/en/1.6/topics/db/aggregation/