Multiple Annotates / filters

Using Django Using the ORM
1

Hi,

I’m looking to create a table with data over different time periods. 1 day and 1 week.

My function works to return the data for the week, but I’d also like to combine the same function to show me the data for 1 single day.

I’ve tried creating different functions 1 for weekly results and 1 for daily, but I can’t get this to display in a single table, which I’m thinking its because they are different query sets and looping through each one seems to populate all of the results of the week query set into each row.

My view:

def get_average_interactions_by_user():

    # Get the date range for the previous week
    today = get_london_time().date()
    start_of_week = today - timedelta(days=today.weekday())
    start_of_previous_week = start_of_week - timedelta(days=7)
    end_of_previous_week = start_of_week - timedelta(days=1)

    # Query the Usergrowth model for the required data, grouped by project
    results = usergrowth_data = Usergrowth.objects.filter(
        capture_date__gte=start_of_previous_week,
        capture_date__lte=end_of_previous_week
    ).values('project__name').annotate(
        total_interactions=Sum('interactions',distinct=True),
        total_user_count=Sum('user_count', distinct=True)
    )

My issue here I think is because I am filtering on the start/end dates which don’t include today’s data.

my table in my template is:

<tbody>
      {% for project in project_data  %}                         
      {% load humanize %}
      <tr>
        <td class="table-text">{{ project.project}}</td>
        <td class="table-text table-row-text ">{{ project.user_count | intcomma }}</td>
        <td class="table-text">{{ project.interactions}}</td>         # This is for 1 day              
        <td class="table-text">
        {% for project2 in results %}
            {% if project.project == project2.project__name %}
            {{ project2.total_interactions }} # Im trying to get 7 days here
            {% endif %}
        {% endfor %}
        </td>    
        <td class="table-text"></td>    
        <td class="table-text"></td>    
        <td class="table-text"></td>                              
      </tr>                     
      {% endfor %}
    </tbody>

I’m trying to get:

project  | user count | interactions (day) | interactions (Week)

project1 | 10         | 50                 | 100
project2 | 500        | 82                 | 1000
...

Hope that makes sense.

Tom.

2

Just as a quick off-the-cuff idea, you may want to create those values for the annotations as subqueries. e.g. .annotate(value1=Subquery(…), value2=Subquery(…))

3

Ah, didn’t think about that.

So i would remove the initial filter

filter(
        capture_date__gte=start_of_previous_week,
        capture_date__lte=end_of_previous_week
    )

and move those into subqueries? is that what you thinking?

Cause I’m thinking that applying the filter is going to mean the results returned are based on that time frame. Or is it possible to retrieve daily data outside of that filter?

4

is this on the right track?

project_data = Usergrowth.objects.filter(capture_date=current_date).select_related('project')
weekly_sums = Usergrowth.objects.filter(
        capture_date__gte=start_of_previous_week,
        capture_date__lte=end_of_previous_week
    ).values('project__name').annotate(
        total_interactions=Sum('interactions'),
        todays_user_count=Subquery(project_data.values('user_count')),
        todays_interactions=Subquery(project_data.values('interactions'))
    )

this throws:
ProgrammingError at /tables more than one row returned by a subquery used as an expression

5

What are these expressions going to return?

What are you looking for them to return? Is it supposed to be a total like a Sum or Count of those values?

It’s easiest to build something like this if you create the initial query for just that expression to ensure you can get the desired value, then work that into the subquery structure.

Also note that in a subquery, you may want to use an OuterRef to filter the query to match the row for which you’re trying to obtain a value.

6

I’m expecting this to just return the value in the database for the current_date It’s not a sum, just a database value.

7

So you’re saying that this query:
Usergrowth.objects.filter(capture_date=current_date).select_related('project')
can only ever return 1 row?

8

Ah, no! Sorry it will return 100s. Every project with a capture date of today

9

So what query do you want to run to only retrieve one row? That’s the additional filter that you need to add to your Subquery.

10

This seems to work.
project_data = Usergrowth.objects.filter(capture_date=current_date, project__name=OuterRef('project__name')).order_by('-capture_date').values('user_count', 'interactions')[:1]

Not sure if this is the best way, but it works

1 Like
11

If it works and doesn’t eat your machine in the process, then it’s good enough.