Displaying an Advanced Aggregated MySQL query as a list either from Raw SQL or from a MySQL View

Using Django
1

I am working on a project that deals with capacity and resource planning for a small business. I have built an app using a Django that facilitates collection of data for assigned tasks and target goals and then compares that to user entries made against tasks assigned to them. The query for the Finance department should display as two tables on a single page.

After researching this topic for some time, I have found two ways that sound best for dealing with the specific SQL queries needed:

  1. Use the Raw SQL option for Django to create the lists.
  2. Create a View in MySQL and set the model in the model file as a managed=False model.

There are several problems impeding progress with this query that seem to stem from the need query sets have in Django to have a primary key value.

SQL Queries

The following are the two queries as they are executed successfully in the MySQL Workbench:

SELECT 
    projects.project_number AS 'project', 
    projects.start_date AS 'start_date', 
    projects.target_date AS 'target_date', 
    SUM(tasks.target_hours) AS 'target_hours',
    SUM(activities.hours) AS 'billed_hours', 
    (SUM(tasks.target_hours) - SUM(activities.hours)) AS 'remaining_hours' 
FROM activities
        INNER JOIN tasks ON activities.task_id = tasks.id 
        INNER JOIN projects ON tasks.project_id = projects.id 
            GROUP BY projects.project_number, tasks.task_number, activities.activity_class_id
                ORDER BY projects.target_date DESC, projects.project_number ASC;


SELECT 
        projects.project_number AS 'project', 
        CONCAT(users.first_name, ' ', users.last_name) AS 'employee', 
        SUM(activities.hours) AS 'hours' 
FROM activities 
        INNER JOIN tasks ON activities.task_id = tasks.id 
        INNER JOIN users ON activities.assignee_id = users.id 
        INNER JOIN projects ON tasks.project_id = projects.id 
            GROUP BY projects.project_number, users.username 
                ORDER BY projects.project_number ASC, users.username ASC;

Case 1

In the first scenario, the view file was given the following function-based view:

@login_required
def qcpat_reports(request):

    # queries
    
    qry_time_report = "SELECT (1) AS 'id', projects.project_number AS 'project', projects.start_date AS 'start_date', " \
        "projects.target_date AS 'target_date', SUM(tasks.target_hours) AS 'target_hours', " \
        "SUM(activities.hours) AS 'billed_hours', (SUM(tasks.target_hours) - SUM(activities.hours)) " \
        "AS 'remaining_hours' " \
        "FROM activities " \
        "INNER JOIN tasks ON activities.task_id = tasks.id " \
        "INNER JOIN projects ON tasks.project_id = projects.id " \
        "GROUP BY projects.project_number, tasks.task_number, activities.activity_class_id " \
        "ORDER BY projects.target_date DESC, projects.project_number ASC;"

    qry_personnel_totals = "SELECT (1) AS 'id', projects.project_number AS 'project', " \
        "CONCAT(users.first_name, ' ', users.last_name) AS 'employee', " \
        "SUM(activities.hours) AS 'hours' " \
        "FROM activities " \
        "INNER JOIN tasks ON activities.task_id = tasks.id " \
        "INNER JOIN users ON activities.assignee_id = users.id " \
        "INNER JOIN projects ON tasks.project_id = projects.id " \
        "GROUP BY projects.project_number, users.username " \
        "ORDER BY projects.project_number ASC, users.username ASC;"

    # execution of queries

    time_remaining = models.Activity.objects.raw(qry_time_report)
    personnel_totals = models.Activity.objects.raw(qry_personnel_totals)

    return render(request, 'qcpat/reports.html', {'time_remaining':time_remaining, 'personnel_totals':personnel_totals})

Note: The " (1) AS ‘id’ " phrasing was added to each SQL query in this view based on advice from a Stack Exchange post that dealt with the error when a Query Set does not have a specified primary key.

Case 2

In the second case, a view was created with the SQL queries in MySQL. MySQL does not create an id column in a view. It simply displays the query and resources I’ve found so far indicate that you cannot add a custom value to a view in MySQL. Some people have suggested a called procedure to attempt making a view with one for a post that regarded that specific feature in MySQL.

Since the data source does not have an id for a primary key, then it throws the same errors when called in the following view with the supporting model from the model file:

# Model File

class TimeRemainderReportView(models.Model):
    project = models.CharField(max_length=250)
    start_date = models.DateField()
    target_date = models.DateField()
    target_hours = models.IntegerField()
    billed_hours = models.IntegerField()
    remaining_hours = models.IntegerField()
    
    class Meta():
        db_table = 'timeRemainders'
        managed = False

class PeopleTimeReportView(models.Model):
    project = models.CharField(max_length=250)
    employee = models.CharField(max_length=250)
    hours = models.IntegerField(max_length=250)
    
    class Meta():
        db_table = 'peopleTime'
        managed = False

# View File

@login_required
def qcpat_reports(request):

    time_remaining = models.TimeRemainderReportView.objects.all()
    personnel_totals = models.PeopleTimeReportView.objects.all()

    return render(request, 'qcpat/reports.html', {'time_remaining':time_remaining, 'personnel_totals':personnel_totals})

Template File

The template file remains the same in either case, but continues to throw errors or display no data.

{% extends 'main_base.html' %}
{% load static %}

{% block titleblock %}QC Time Reports{% endblock %}

{% block content %}

    <div class="row g-3">
        <div class="col-md-12">
            <div class="card">
                <div class="card-body">
                    <h5 class="card-title">Project Time Utilized</h5>
                    <div class="row">
                        <div class="col-md-12"><br></div>
                        <div class="col-md-12">
                            <table class="table table-striped">
                                <thead>
                                    <th>Project #</th>
                                    <th>Start Date</th>
                                    <th>Target Date</th>
                                    <th>Target Hours</th>
                                    <th>Billed Hours</th>
                                    <th>Remaining Hours</th>
                                </thead>
                                <tbody>
                                    {% for entry in time_remaining %}
                                    <tr>
                                        <td>{{ time_remaining.project }}</td>
                                        <td>{{ time_remaining.start_date }}</td>
                                        <td>{{ time_remaining.target_date }}</td>
                                        <td>{{ time_remaining.target_hours }}</td>
                                        <td>{{ time_remaining.billed_hours }}</td>
                                        <td>{{ time_remaining.remaining_hours }}</td>
                                    </tr>
                                    {% endfor %}
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
        <div class="col-md-12">
            <div class="card">
                <div class="card-body">
                    <h5 class="card-title">Project Hours Billed by Personnel</h5>
                    <div class="row">
                        <div class="col-md-12"><br></div>
                        <div class="col-md-12">
                            <table class="table table-striped">
                                <thead>
                                    <th>Project #</th>
                                    <th>Employee</th>
                                    <th>Hours</th>
                                </thead>
                                <tbody>
                                {% for entry in personnel_totals %}
                                    <tr>
                                        <td>{{ personnel_totals.project }}</td>
                                        <td>{{ personnel_totals.employee }}</td>
                                        <td>{{ personnel_totals.hours }}</td>
                                    </tr>
                                {% endfor %}
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>

{% endblock %}

Testing and Results

The closest to visualizing the data so far was the use of Case 1 with the added " (1) as ‘id’ " component. The results showed two tables with the correct number of rows for the query results; however, there was nothing printed in the table.

Testing the code in the shell with the following command demonstrates that the same value is being repeatedly used from the query as each entry for the table.

>>>qry_time_report = "SELECT (1) AS 'id', projects.project_number AS 'project', projects.start_date AS 'start_date', " \
...         "projects.target_date AS 'target_date', SUM(tasks.target_hours) AS 'target_hours', " \
...         "SUM(activities.hours) AS 'billed_hours', (SUM(tasks.target_hours) - SUM(activities.hours)) " \
...         "AS 'remaining_hours' " \
...         "FROM activities " \
...         "INNER JOIN tasks ON activities.task_id = tasks.id " \
...         "INNER JOIN projects ON tasks.project_id = projects.id " \
...         "GROUP BY projects.project_number, tasks.task_number, activities.activity_class_id " \
...         "ORDER BY projects.target_date DESC, projects.project_number ASC;"
>>> qry_personnel_totals = "SELECT (1) AS 'id', projects.project_number AS 'project', " \
...         "CONCAT(users.first_name, ' ', users.last_name) AS 'employee', " \
...         "SUM(activities.hours) AS 'hours' " \
...         "FROM activities " \
...         "INNER JOIN tasks ON activities.task_id = tasks.id " \
...         "INNER JOIN users ON activities.assignee_id = users.id " \
...         "INNER JOIN projects ON tasks.project_id = projects.id " \
...         "GROUP BY projects.project_number, users.username " \
...         "ORDER BY projects.project_number ASC, users.username ASC;"
>>> time_remaining = models.Activity.objects.raw(qry_time_report)
>>> personnel_totals = models.Activity.objects.raw(qry_personnel_totals)

>>> for entry in time_remaining:
...     print(entry)
...
Since short nice kitchen president.
Since short nice kitchen president.
Since short nice kitchen president.
Since short nice kitchen president.
Since short nice kitchen president.
Since short nice kitchen president.
Since short nice kitchen president.
Since short nice kitchen president.
Since short nice kitchen president.
Since short nice kitchen president.
Since short nice kitchen president.
Since short nice kitchen president.
Since short nice kitchen president.
Since short nice kitchen president.
Since short nice kitchen president.
Since short nice kitchen president.
Since short nice kitchen president.
Since short nice kitchen president.
Since short nice kitchen president.
Since short nice kitchen president.
Since short nice kitchen president.
Since short nice kitchen president.
Since short nice kitchen president.

...

Scope of Question

What is the best method to use for this particular scenario in getting each of the objects to display correctly? Has anyone had experience with these particular kinds of queries in Django? Is there something better to use or would jQuery be easier. Please advise.

2

First, while a queryset is expecting to have a field identified as the primary key, strictly speaking it’s not required to have a truly unique primary key when executing select-only queries. It’s only really necessary if any updates are going to be performed.
I have many views that don’t return a true primary key. I just flag a reasonable column with the primary key attribute and go on my way. (This is after ensuring that managed = False and that nothing is going to be attempted to update via that view.)

You also have the ability to create your query as an ORM-based query using Aggregation to provide the group by clause. (I’m sure there’s a way to build that query through the orm, but I’d want to play around with it a little bit to make sure I was getting the right results before offering a solution.)

3

Yes, I agree with this suggestion and response. I was hesitant to use ORM with a query this particular because the output has to be very specific to meet the needs of those using it. Timewise, it seemed raw was the way to go as opposed to continually testing ORM options.

I will look into seeing if I can flag a different item as the primary key though…that might correct the issue.

4

Unless there’s something else involved that you have not yet disclosed, I don’t see where this applies at all. It’s not like you’re returning the results of the query directly to the browser through, say, an AJAX call.

In both cases, the data returned from the query is being used to populate a template, which can be built to render the data the same way in either case.

5

No, these views should function the same way each time.

6

So, I set the project to the primary_key and I am now getting an output in the shell that makes sense; however, I’m still getting a blank list of rows in the template when the view returns. It seems the values just don’t show even when trying a class-based list. Same behavior.

7

There may be other issues needing to be address, but one factor here is that you’re referencing the wrong objects inside your loop.

You’re looping over time_remaining, assigning each element to entry in turn.
However, in your template, you’re trying to render fields such as time_remaining.project. But time_remaining isn’t an object, it’s a queryset. The identifier entry is the object inside the loop.
You should be rendering entry.project.

9

Ok. I did go back through the code and after several different ways working with the QuerySet from the view in MySQL and trying the alternative without the data posting correctly, I found it easier to tackle the issue utilizing a different way of accessing the data. I used a technique for DataFrames to render to the template looping the entry.project method.

New View File Configuration

@login_required
def qcpat_reports(request):

sql_host = 'secret'
    sql_port = secret
    sql_user = 'secret'
    sql_password = 'secret'
    sql_db = 'secret'
    
    cnx = pymysql.connect(
        host=sql_host,
        port=sql_port,
        user=sql_user,
        password=sql_password,
        database=sql_db
    )
    
    df1 = pd.read_sql('''SELECT * FROM timeRemainders;''', cnx)
    df2 = pd.read_sql('''SELECT * FROM peopleTime;''', cnx)
    
    df3 = pd.pivot_table(df1, values=['target_hours','billed_hours','remaining_hours'], index=['project'], aggfunc=np.sum)

    cnx.close()

    # parse DataFrame to JSON Format

    json_records_timegap = df3.reset_index().to_json(orient='records', date_format='epoch')
    data1 = []
    data1 = json.loads(json_records_timegap)

    json_records_hours = df2.reset_index().to_json(orient='records', date_format='epoch')
    data2 = []
    data2 = json.loads(json_records_hours)
    
    # render results

    context = {'timegap_report':data1, 'hours_reports':data2}

    return render(request, 'qcpat/reports.html, context)

New HTML Template

...
<tbody>    
     {% for item in timegap_report %}
         <tr>
               <td>{{ item.project }}</td>
               <td>{{ item.target_hours }}</td>
               <td>{{ item.billed_hours }}</td>
               <td>{{ item.remaining_hours }}</td>
         </tr>
    {% endfor %}
</tbody>
...
 <tbody>
     {% for entry in hours_report %}
         <tr>
               <td>{{ entry.project }}</td>
               <td>{{ entry.employee }}</td>
               <td>{{ entry.hours }}</td>
         </tr>
    {% endfor %}
</tbody>
...

This solution produced a viable result. It also has advantages for doing more work with the data in the event more pivot tables are desired. It also worked well with the MySQL view.