Using Peewee ORM to generate data for graphs

manpreet Best Answer 2 years ago

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.

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

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-
                                                

                                                                                                        
                                                    
                                                
                                                


                                                    

                                                        
                                                            0
                                                            views 

                                                            0
                                                                likes 
                                                            
                                                            

                                                            0
                                                            shares

                                                            


    

         Facebook
        
         Twitter
        
        
         Linked In 
        
          WhatsApp