How to filter Highchart plot Data based on Many-to-One relationship?

Question

I have this Django App where I'm showing some data using Highchart (just an example here).

I have two separate views, one where you perform the Filter on the Product table (among other things), and the other view builds the Json from History Table to "pass" to the AJAX function for the plot (The real data is quite heavy).

The data is based on a History table where I have product_id, year, quantity (I want to show the Quantity over Time).

In my model I also have a table for Products with products, category (each product has a category, multiple products can share the same category).

The two tables have a one-to-many relationship with the field product.

In my template I'd like the user to be able to filter the products by the category field (ie: filter products with category 'A'), and this filter should also update the Chart (ie: I want to see the history for just the products in category 'A').

Below my code, I've tried many attempts but none has worked so far.

Please let me know if the code has all the info you need, I've tried to take just the essential.

models.py

class Products(models.Model):
    product = models.TextField(primary_key=True)
    category = models.TextField(blank=True,null=True)
    
    # ...
    
class HistoryProducts(models.Model):
    product_id = models.ForeignKey(Products)
    year = models.TextField(blank=True, null=True)
    quantity = models.DecimalFeld(..)
    
    # ...

filters.py

import django_filters
class ProductsFilter(django_filters.FilterSet):
    category_contains = CharFilter(field_name='category', lookup_expr='icontains') 
    class Meta:
         model = Products
         fields = ['category']

views.py

def index(request):
    products = Products.objects.all()
    myFilter = ProductsFilter(request.GET, queryset=products)
    products = myFilter.qs
    
    # ...
    
    return render(request, 'index.html', context={..})
    
def chart_data(request):
    
    # maybe here we should filter History by myFilter, but can't find how
    # ...
    
    # calculate total quantity
    history = HistoryProducts.objects.values('year').order_by('year').annotate(Total=Sum('quantity'))
    
    
    chart = {
        'chart': {'type': 'column'},
        'title': {'text': 'Quantity by Year'},
        'series': [{
            'name': 'Quantity',
            'data': list(map(lambda row: {'name': round(row['year']),'y': round(row['Total'])}, history))
        }]
    }
    
    return JsonResponse(chart)

index.html

<!-- Filter -->
<form method="GET">
   {{myFilter.form}}
   <button type="submit"> Filter</button>
</form>

<!-- Chart -->
<div>
    <div id="container" data-url="{% url 'chart_data' %}"></div>
</div>

<!-- Scripts -->
<script src="https://code.highcharts.com/highcharts.src.js"></script>
<script>
  // highchart function
  $.ajax({
    url: $("#container").attr("data-url"),
    dataType: 'json',
    success: function (data) {
      Highcharts.chart("container", data);
    }
  });
</script>

---

I guess my question is: is it possibile to "connect" the same Form Filter built with django-filters to multiple models?

Or more in general how would someone takle this kind of problem? I'm open to any suggestion. Thanks

EDIT ---

I have found a solution that is not pretty, and also makes the page much slower.

views.py

def index(request):
    products = Products.objects.all()
    myFilter = ProductsFilter(request.GET, queryset=products)
    products = myFilter.qs

    # get the id filtered
    ids = []
    qs = products.values_list('products',flat=True)
    for i in qs:
        ids.append(i)

    # use ids to filter History
    history = History.objects.filter(product_id_in=ids).values('year').order_by('year').annotate(Total=Sum('quantity'))

    # make the json here
    chart = {
        'chart': {'type': 'column'},
        'title': {'text': 'Quantity by Year'},
        'series': [{
            'name': 'Quantity',
            'data': list(map(lambda row: {'name': round(row['year']),'y': round(row['Total'])}, history))
        }]
    }
    dump = json.dumps(chart)

    # return the json to the template

    return render(request, 'index.html', context={..})

Now I only need this part in the template:

<script>
  Highcharts.chart('container', {{ chart|safe }});
</script>

Basically I moved the Json inside the same view where I filter the data, but this is much much slower.

Answer 1

That's more of a jQuery/Javascript question but it should be possible to capture the form submission assuming you're assigning an id attribute to its HTML element and then use $.getJSON to GET the data from the chart_data view.

https://api.jquery.com/submit/

e.g.

// Assuming you assigned id="filter-form" to your form
$('#filter-form').submit(function(event){
  event.preventDefault();
  var url = $("#container").attr("data-url");
  var formData = $('#filter-form').serialize()
  $.getJSON(url, formData, function(data){
      Highcharts.chart("container", data);
  })
});

You'll then be able to use ProductsFilter from request.GET in your chart_data just like you did in index

def chart_data(request):
    
    products = Products.objects.all()
    myFilter = ProductsFilter(request.GET, queryset=products)
    products = myFilter.qs
    HistoryProducts.objects.filter(
        product_id__in=products
    ).values('year').order_by('year').annotate(
        Total=Sum('quantity')
    )
    
    
    chart = {
        'chart': {'type': 'column'},
        'title': {'text': 'Quantity by Year'},
        'series': [{
            'name': 'Quantity',
            'data': list(map(lambda row: {'name': round(row['year']),'y': round(row['Total'])}, history))
        }]
    }
    
    return JsonResponse(chart)