Dealing with Many-to-one relationships, my attempt. Is there a batter way?

[jamiegau]

Hi, I have a datatable that represents facilities. They can have multiple addresses. Physical, Postal. I want to display the name of the facility and the address as a single field, adding the table columns state, province etc together.

I tried all I could.. ending with this HACK as described below. Can people commend if there is a better way to do this.

I use a SerializerMethodField

class FacilityVShortSerializer(serializers.ModelSerializer):
    FacilityAddress = FacilityAddressShortSerializer(many=True)
    physical_address = serializers.SerializerMethodField('get_physical_address', read_only=True)

    def get_physical_address(self, obj):
        res = ''
        fa = obj.FacilityAddress.all().filter(sType='Physical').first()
        res += (fa.sStreetAddress + ', ' + fa.sCity +
                ', ' + fa.sProvince + ', ' + fa.sCountry)
        return res

    class Meta:
        model = Facility
        fields = ('id',
                  'sAnnotationText',
                  'sFacilityID',
                  'sFacilityName',
                  'sCircuit',
                  'FacilityAddress',
                  'physical_address'
                  )
        depth = 1
        ordering = ['sFacilityName']
        datatables_always_serialize = ('id')

I am using svelte to build my front end. Looks like this.

const fGridOptions = {
		ajax: function(data, callback, settings) {
			let ai = createAxiosInstance();
			ai.get('/dcns/FacilityVShort/?format=datatables', {
				params: data
			})
				.then(response => {
					callback(response.data);
				})
				.catch(error => {
					console.error('Error fetching data:', error);
					showToast('Error fetching data', 'error fetching Facilties: ' + error, 'error');
				});
		},
		columns: [
			{ title: 'Facility Name', data: 'sFacilityName', searchable: true },
			{ title: 'Circuit', data: 'sCircuit', searchable: true },
			{ title: 'Address', data: 'physical_address', visible: true },
],
...

To get the FILTER above the physical_address column to work.. I do the following.. I get the column search string, use it to filter the result my own way and then remove the search string before sending it on to datatables... see below.

class FacilityVShort_ViewSet(viewsets.ModelViewSet):
    queryset = Facility.objects.all()
    serializer_class = FacilityVShortSerializer
    # pagination_class = SetPaginationTen
    pagination_class = dt_pagination.DatatablesPageNumberPagination
    permission_classes = (IsAuthenticated,)

    def get_queryset(self):
        
          search_str = self.request.query_params.get('columns[2][search][value]')
          # clear the parameter so it doesn't get passed to the filter
          query_params = self.request.query_params.copy()
          query_params['columns[2][search][value]'] = ''
          self.request._request.GET = query_params
          
          search_array = search_str.split(' ')
          query = Q()
          if search_str is not None:
              for cs in search_array:
                  #
                  # get FacilityAddress where sType = 'Physical' and search the address fields
                  #
                  query &= (Q(FacilityAddress__sType='Physical') &
                            (Q(FacilityAddress__sStreetAddress__icontains=cs) |
                             Q(FacilityAddress__sCity__icontains=cs) |
                             Q(FacilityAddress__sProvince__icontains=cs) |
                             Q(FacilityAddress__sPostalCode__icontains=cs) |
                             Q(FacilityAddress__sCountry__icontains=cs)))
                  # query &= (Q(FacilityAddress__sType='Physical') &
                  #           (Q(FacilityAddress__sCountry__icontains=cs) || Q(FacilityAddress__sCity__icontains=cs) ||)
              return Facility.objects.filter(query)
          else:
              return Facility.objects.all()

This seems like a huge HACK.

Can other comment on this method, and if there is a better way?

[StevenMapes]

So for filtering what I tend to do is to use annotations on the queryset then create a custom filte4 with a class attribute of the name of the annotation that way you can filter on it. But it depends if you can convert the method in the serializer into an annotation, if you can then you also won't need the serializer method as the annotation will produce the result.

I've used it mainly with COUNT, VALUE, CONCAT, COALESCE and GROUP_CONCAT though