Hi, another question regarding queries (due to being new and size of table (~300M rows), I am quickly finding myself out of depth with the ORM!)
I am trying to create a page that displays the raw sensor data for a specific device (modem). I am using django-filter and pagination which is…sort of working.
I have a very similar page from the summary data model (~30k rows) which is using the same pattern to the one below, and it is working ok. I can filter the displayed data on each device using the filter form (which is loaded from a device model which gives me a list of devices), and move through pages using pagination. Although I have a suspicion there is a better way than the way I am doing it with a getElementById script?
However on the sensor data page, due to the size of the table, my laptop is running out of memory (64Gb) before displaying the page.
On the initial load, it seems to be loading the entire table (I notice the filter form defaults to empty value, therefore all data).
Is there a way to stop it doing this? All I want is 100 rows at a time even on initial load.
models.py:
class Sensordata(models.Model):
row_id = models.AutoField(primary_key=True)
device = models.ForeignKey(Devices, models.DO_NOTHING)
api_param = models.ForeignKey(Apiparams, models.DO_NOTHING)
timestamp = models.DateTimeField(db_column='TimeStamp')
value = models.CharField(db_column='Value', max_length=255, blank=True, null=True)
class Meta:
managed = False
db_table = 'sensordata'
verbose_name = 'Sensor Data'
verbose_name_plural = 'Sensor Data'
def __str__(self):
return self.device
filters.py:
class SensordataDevicesFilter(django_filters.FilterSet):
device = django_filters.ModelChoiceFilter(queryset=Devices.objects.order_by('serialno').all())
class Meta:
model = Sensordata
fields = ['device', ]
views.py:
def sensordata_list(request):
queryset = Sensordata.objects.select_related("device__machine","api_param__param").order_by('timestamp')
filtered_qs = SensordataDevicesFilter(request.GET, queryset)
filtered_qs_form = filtered_qs.form
filtered_qs = filtered_qs.qs
paginator = Paginator(filtered_qs, 100)
page = request.GET.get('page')
try:
response = paginator.page(page)
except PageNotAnInteger:
response = paginator.page(1)
except EmptyPage:
response = paginator.page(paginator.num_pages)
return render(request, 'modem_api/sensordata_list.html', {'filter': response, 'filtered_qs_form':filtered_qs_form})
sensordata_list.html:
{% extends "_base.html" %}
{% load crispy_forms_tags %}
{% block title %}Sensor Data{% endblock title %}
{% block content %}
<h1>Sensor Data List</h1>
<div class="bg-dark text-white rounded py-3 px-5">
<div class="pagination">
<span class="step-links">
{% if filter.has_previous %}
<a href="?device={{ selectItem.value }}&page={{ filter.previous_page_number }}">previous</a>
{% endif %}
<span class="current">
Page {{ filter.number }} of {{ filter.paginator.num_pages }}.
</span>
{% if filter.has_next %}
<a href="?device={{ selectItem.value }}&page={{ filter.next_page_number }}">next</a>
{% endif %}
</span>
</div>
<div class="row">
<div class="col-md-3">
<form method="GET">
{{ filtered_qs_form|crispy }}
<button type="submit">Search</button>
</form>
<h2>{{ filter.0.device.serialno }}</h2>
<h2>{{ filter.0.device.machine.machineserialno }}</h2>
</div>
<div class="col-md-9">
<table class="table table-striped table-dark">
<tr>
<th>API Param</th>
<th>Value</th>
<th>Timestamp</th>
</tr>
{% for obj in filter %}
<tr>
<td>{{ obj.api_param.param.parameter }}</td>
<td>{{ obj.value }}</td>
<td>{{ obj.timestamp }}</td>
</tr>
{% empty %}
<li>No devices yet.</li>
{% endfor %}
</table class="table">
</div>
</div>
<script>
var e = document.getElementById("id_device");
var device_num = e.options[e.selectedIndex].value;
</script>
{% endblock content %}