sql code to django

Question

I have a SQL query:

select name, min(time(eventime)), max(time(eventime)) 
from events 
 inner join emp 
 on empid = id 
group by date(eventime) 

How do I make this work in Django?

My models is:

class Emp(models.Model):
 id = models.IntegerField(primary_key=True)
 lastname = models.CharField(max_length=192)
 firstname = models.CharField(max_length=192, blank=True)
 midname = models.CharField(max_length=96, blank=True)
class Events(models.Model):
 serialid = models.IntegerField(primary_key=True)
 empid = models.IntegerField()
 cardnum = models.IntegerField()
 eventime = models.DateTimeField() 

• ewok: what do your models look like?
• Alasdair: Are you sure the sql query is correct? There is no name field in your Django models. It looks like you might want to group by emp.id as well as date.

Answer 1

You can execute pretty much any SQL query like this:

from django.db import connection
cursor = connection.cursor()
sql = 'select name, min(time(eventime)), max(time(eventime)) ' \
 'from abc_table group by date(eventime)'
cursor.execute(sql)
retval = cursor.fetchall()

For more information see the Django docs on executing custom sql directly.

• ewok: While this is possible, it is bad form to use raw SQL in Django. Django is designed for the specific purpose of hiding the SQL and making the database easier to work with.
• Alasdair: I disagree with the downvote. I don't think this particular SQL query maps to an ORM query easily (although I'm happy to be proved wrong). In cases like this, there's no shame in dropping back to raw SQL.
• ayanami: @ewok: Django ORM is created for the specific purpose of simplifying CRUD, and given more or less complex query you are forced to go back to plain old SQL. I believe that the original poster’s query actually can be converted to ORM, but it won’t get any more readable from doing so, and it doesn’t map to a model anyway.