javascript - 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.

标签： javascriptpythonjquerydjango

Answer 1

这更像是一个 jQuery/Javascript 问题，但应该可以捕获表单提交，假设您id为其 HTML 元素分配一个属性，然后使用视图$.getJSON中GET的数据chart_data。

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

例如

// 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);
  })
});

然后您就可以ProductsFilter像在request.GETchart_dataindex

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)