#StackBounty: #python #django #ajax How to filter Highchart plot Data based on Many-to-One relationship?

Bounty: 200

I have this 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 -->
https://code.highcharts.com/highcharts.src.js
<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.


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.