I’m currently working on a Django project where I need to display and sort weather station data in a DataTable. I’m facing an issue when it comes to sorting columns that contain calculated values, such as averages and sums.
this code result in clickhouse error due to a problem with the inner join not working as properly I am out of ideas to sort the values (avg_value, max_value,min_value, sum_value, and count_value) please help
@cache_page(120)
@api_view(['GET'])
def station_list(request):
serializer = StationListSerializer(data=request.query_params)
serializer.is_valid(raise_exception=True)
# Extracting DataTables parameters
draw = int(request.GET.get('draw', 1))
start = int(request.GET.get('start', 0))
length = int(request.GET.get('length', 10))
order_column_index = int(request.GET.get('order[0][column]', 0))
order_direction = request.GET.get('order[0][dir]', 'asc')
search_value = request.GET.get('search[value]', '')
selected_region = request.GET.get('region')
selected_governorate = request.GET.get('governorate')
# QuerySet for AwsGov
station_queryset = AwsGov.objects.all()
if selected_region:
station_queryset = station_queryset.filter(ADMIN=selected_region)
if selected_governorate:
station_queryset = station_queryset.filter(REGION_ID=selected_governorate)
# Define columns for sorting and filtering
direct_fields = ['station_id', 'Name', 'ADMIN_NA_1', 'REGION_N_1']
all_fields = direct_fields + ['avg_value', 'max_value', 'min_value', 'sum_value', 'count_value']
# Adjust for language
if request.LANGUAGE_CODE == 'ar':
direct_fields = ['station_id', 'Name', 'ADMIN_Name', 'REGION_NAM']
if order_column_index < len(all_fields):
order_column = all_fields[order_column_index]
else:
order_column = all_fields[0]
# else:
# order_column = columns[0] # Default to the first column if out of range
if order_column in direct_fields:
if order_direction == 'desc':
order_column = f'-{order_column}'
station_queryset = station_queryset.order_by(order_column)
#station_queryset = station_queryset.order_by(order_column)
# Fetch paginated station data
#paged_station_data = list(station_queryset[start:start + length].values(*columns))
# Fetch and process weather data
varid = request.GET.get('varid', 3)
start_time_str = request.GET.get('start_time', None)
end_time_str = request.GET.get('end_time', None)
if start_time_str and end_time_str:
start_time = dateparse.parse_datetime(start_time_str)
end_time = dateparse.parse_datetime(end_time_str)
else:
start_time = timezone.now().replace(microsecond=0) - timedelta(hours=24)
end_time = timezone.now().replace(microsecond=0)
weather_data = {}
# Annotate with weather data
weather_annotations = {
'avg_value': Coalesce(Avg('value'), 0, output_field=FloatField()),
'max_value': Coalesce(Max('value'), 0, output_field=FloatField()),
'min_value': Coalesce(Min('value'), 0, output_field=FloatField()),
'sum_value': Coalesce(Sum('value'), 0, output_field=FloatField()),
'count_value': Coalesce(Count('value'), 0, output_field=IntegerField())
}
# Begin by annotating the station_queryset with all weather data
avg_subquery = Subquery(
Aws.objects.filter(
stationid=OuterRef('id'), # Assuming 'id' is the field in AwsGov matching Aws.stationid
varid=varid,
meastime__range=(start_time, end_time)
).annotate(avg=Avg('value')).values('avg')[:1],
output_field=FloatField()
)
max_subquery = Subquery(
Aws.objects.filter(
stationid=OuterRef('id'),
varid=varid,
meastime__range=(start_time, end_time)
).annotate(max=Max('value')).values('max')[:1],
output_field=FloatField()
)
min_subquery = Subquery(
Aws.objects.filter(
stationid=OuterRef('id'),
varid=varid,
meastime__range=(start_time, end_time)
).annotate(min=Min('value')).values('min')[:1],
output_field=FloatField()
)
sum_subquery = Subquery(
Aws.objects.filter(
stationid=OuterRef('id'),
varid=varid,
meastime__range=(start_time, end_time)
).annotate(sum=Sum('value')).values('sum')[:1],
output_field=FloatField()
)
count_subquery = Subquery(
Aws.objects.filter(
stationid=OuterRef('id'),
varid=varid,
meastime__range=(start_time, end_time)
).annotate(count=Count('value')).values('count')[:1],
output_field=IntegerField()
)
station_queryset = AwsGov.objects.annotate(
avg_value=avg_subquery,
max_value=max_subquery,
min_value=min_subquery,
sum_value=sum_subquery,
count_value=count_subquery
)
print(station_queryset)
# Determine the field to sort by based on the frontend request
order_column = all_fields[order_column_index] if order_column_index < len(all_fields) else all_fields[0]
if order_direction == 'desc':
order_column = f'-{order_column}'
# Sort the annotated queryset
station_queryset = station_queryset.order_by(order_column)
# Fetch the required page of the station data
paged_station_data = list(station_queryset[start:start + length].values(*direct_fields))
# The weather data is already included in the paged_station_data, so you don't need to merge it again
# Prepare the final response
response_data = {
"draw": draw,
"recordsTotal": AwsGov.objects.count(),
"recordsFiltered": station_queryset.count(),
"data": paged_station_data,
}
return Response(response_data)
here is the code from the frontend in vue js:
initTable() {
const LANGUAGE_CODE = "{{ request.LANGUAGE_CODE }}";
const vm = this;
this.dataTable = $('#combined').DataTable({
responsive: true,
processing: true,
serverSide: true,
searching: false,
dom: 'Plfrtip',
ajax: {
url: 'stations/',
type: 'GET',
dataSrc: json => json.data,
error: (xhr, error, thrown) => {
console.error('Error occurred:', xhr, error, thrown);
}
},
columns: [
{ data: "station_id" },
{
data: "Name",
render: function (data, type, row) {
return `<a href="station-details/${row.station_id}/" style="text-decoration: none;">${data} <i class="fas fa-share"></i></a>`;
}
},
{ data: LANGUAGE_CODE === 'ar' ? "ADMIN_Name" : "ADMIN_NA_1" },
{ data: LANGUAGE_CODE === 'ar' ? "REGION_NAM" : "REGION_N_1" },
{
data: "avg_value",
render: function (data, type, row) {
if (type === 'display' || type === 'filter') {
return parseFloat(data).toFixed(2);
}
return data;
}
},
{
data: "max_value",
},
{
data: "min_value",
},
{
data: "sum_value",
},
{ data: "count_value" },
],
});
},