I'm building a web app which allows professors to input assignments for students to follow along with, an interactive syllabus if you will. One of the sections of a course page displays progress.
I have built a pie graph component which I'd like to populate with data:
title,# name of chart
percent,# percentage of assignments completed
count,# how many assignments completed
total # how many assignments total)
I'm using the Peewee ORM to retrieve this data from my assignments table:
name =CharField()# Homework, Test, Final, etc.classAssignment(BaseModel):
I need the following from the database and I'm not sure how to accomplish it with Peewee. Getting the name, and total should be simple. But I need to compare the due_date to today's date to see how many of the assignments are completed.
Name Total Completed
Final 2 0 Homework 23 12 Test 4 2
My pie chart output will look something like this if it matters:
Homework|XXX-------|3 of 10 complete
I've got a query that does almost everything I need. Can anyone help me take it the rest of the way?
Here's the query:
count(a.type_id)as total,(select count(id)from assignment a
where a.course_id =7and a.user_id =3and a.due_date < date()groupby a.type_id
order by a.type_id
from assignment a
inner join type t on t.id = a.type_id
where a.course_id =7and a.user_id =3groupby a.type_id
order by a.type_id
And here's the result using the sample data below:
This is really close, but I would expect the completed column to be specific to the assignment type.
Here's some sample data from the Assignment table
------------------------9,Chapter1,2014-11-0110,Chapter2,2014-11-0811,Test on chapter 1-2,2014-11-1512,Chapter3,2014-
( 5 months ago )
The great thing about dates in python, and peewee in particular is that you can compare them in a pretty intuitive way. Behind the scenes dates are typically represented as number of seconds since some date in 1970. Here's an example of querying by date with peewee: