0

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?

brain storm
  • 30,124
  • 69
  • 225
  • 393
  • Are you asking how to take an existing database and convert it into django models? Its not clear what is the problem you are trying to solve. – Burhan Khalid Jul 21 '14 at 23:51
  • NO I am not asking that. I am asking how a SQL query will look like if I create tables outside of Django. My edit above can explain better I guess – brain storm Jul 22 '14 at 00:00
  • I'm not getting what you want. Do you want to take an existing models.py and then try to create the SQL query by hand to learn how queries are written or what? Your question _"I'm not sure this is how in SQL it is done?"_ - is very confusing because what you pasted _is SQL_. If you ever only used MySQL - you may be surprised to learn that each database provider has their own flavor of SQL, some are more strict (conforming to the [SQL standard](http://en.wikipedia.org/wiki/SQL:2011)) than others. Here is how [MySQL differs](http://dev.mysql.com/doc/refman/5.7/en/differences-from-ansi.html). – Burhan Khalid Jul 22 '14 at 00:03
  • Ok. what I pasted is sql statement that django spits out when I run sqlall myapp. please forget django for a moment. If I want to create two tables shown above, How would I do that in MySQL. It is not clear to me whether I should explicitly use `REFERENCES` and `CREATE INDEX` as shown above or there is short cut within MYsQl which will automatically do `CREATE INDEX` – brain storm Jul 22 '14 at 00:17
  • 1
    I think you confused everyone including yourself by talking about queries in managers and django; when your real question is simply _"Does MySQL automatically create indexes?"_ Edit your question to ask your real question; and then you'll get an answer that will make sense to you. – Burhan Khalid Jul 22 '14 at 13:43

1 Answers1

0

[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/

Alfred Huang
  • 17,654
  • 32
  • 118
  • 189