In my database I have two tables - users and user_properties

I need to filter output using GET parameter phone. But both this tables has column phone and has different values.

When I doing GET request (ex. "?phone=123456789") I need to search User by phone number using not only user_properties.phone, but user.phone too!

I googled and found a way partially to do this using get_queryset(filtering) and SerializerMethodField(to modify output):

models.py

class User(models.Model):

    balance = models.DecimalField(decimal_places=35, max_digits=40)

    fio = models.CharField(max_length=64)

    phone = models.CharField(max_length=64, blank=True, null=True)



    class Meta:

        managed = False

        db_table = 'user'





class UserProperties(models.Model):

    user_id = models.IntegerField(primary_key=True)

    phone = models.CharField(max_length=11)



    class Meta:

        managed = False

        db_table = 'user_properties'

views.py

class UserPropertiesViewSet(viewsets.ModelViewSet):

queryset = UserProperties.objects.all()

serializer_class = serializers.UserPropertiesSerializer



def get_queryset(self):

    queryset = self.queryset

    # phone number from GET

    phone = self.request.query_params.get('phone')



    # Search users matches in user_properties using by phone number

    if phone:

        queryset = UserProperties.objects.all()

        users = queryset.filter(phone__contains=phone)

        return users

    else:

        return queryset

serializers.py

class UserPropertiesSerializer(serializers.ModelSerializer):

all_phones = serializers.SerializerMethodField()



class Meta:

    model = models.UserProperties

    fields = ['user_id', 'phone', 'fio', 'url', 'all_phones',]



# phone numbers from user and user_properties tables

def get_all_phones(self, obj):

    # search phones in <user> table by user_id

    user_phones = models.User.objects.filter(id__exact=obj.user_id).values_list('phone', flat=True)

    # add phones from user_properties table

    result = [obj.phone,]

    # add phones from user table

    for phone in user_phones[0].split(','):

        result.append(''.join(filter(lambda x: x.isdigit(), phone)))



    # return list with all phones

    return set(result)

And I get all_phones column in my filtered results:

{

"count": 1,

"next": null,

"previous": null,

"results": [

    {

        "user_id": 17897,

        "phone": "123456789", <--- user_properties table

        "fio": "....",

        "url": "....",

        "all_phones": [

            "123456789",

            "5512222",

            "49543"

        ] <--- user_properties.phone + user.phone

    }

]

}

But in the get_queryset I filtering only using "real" phone column in user_properties table. How to filter results using "virtual" all_phones column? Or search in two tables and get User who has one of this phone numbers? Is it's possible? Or my way is not correct?

Thanks!

This question is more about the Django ORM than Django REST framework, but rewriting the get_queryset method like this will search on the phone fields of both models:

from django.db.models import Q



...



def get_queryset(self):

    queryset = self.queryset

    # phone number from GET

    phone = self.request.query_params.get('phone')



    if phone:

        queryset = queryset.filter(

            Q(phone__contains=phone)

            | Q(user__phone__contains=phone)

        )



    return queryset