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:
pie_chart(
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:
class Type(BaseModel):
name = CharField() # Homework, Test, Final, etc.
class Assignment(BaseModel):
name = CharField()
due_date = DateField()
type = ForeignKeyField(Type)
course = ForeignKeyField(Course)
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.
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
UPDATE
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:
select t.name,
count(a.type_id) as total,
(
select count(id)
from assignment a
where a.course_id = 7
and a.user_id = 3
and a.due_date < date()
group by a.type_id
order by a.type_id
) as completed
from assignment a
inner join type t on t.id = a.type_id
where a.course_id = 7
and a.user_id = 3
group by a.type_id
order by a.type_id
And here's the result using the sample data below:
Homework, 8, 6
Test, 4, 6
Final, 2, 6
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
id name
------------------------
9, Chapter 1, 2014-11-01
10, Chapter 2, 2014-11-08
11, Test on chapter 1-2, 2014-11-15
12, Chapter 3, 2014-11-19
13, Chapter 4, 2014-11-22
14, Test on chapter 3-4, 2014-11-25
15, Midterm - Chapter 1-4, 2014-11-25
16, Chapter 5, 2014-11-25
17, Chapter 6, 2014-11-25
18, Test on chapter 5-6, 2014-11-25
19, Chapter 7, 2015-01-09
20, Chapter 8, 2015-01-11
21, Test on chapter 7-8, 2015-01-13
22, Final - Chapter 1-8, 2015-01-15
The following is the best I could come up with for now. I'm hardcoding the "completed" column because I can't get it right:
Assignment.select(
Type.name,
fn.Lower('1').alias('completed'),
fn.Count(Type.id).alias('total'),
).join(Type).where(
Assignment.course==self,
).group_by(Type.id).order_by(Type.id)
I think your issue is that your subquery returns more than one row - you should add 'and a.type_id = t.id' to the subquery's where.