Django Filter using Q Objects on two Models

I am working a Django Application where I want to search two Models Profile (surname or othernames fields) and Account (account_number field) using Q objects. From what I did, it is searching only one Model (Account) and any attempt to search any value from the other Model (Profile) triggers an error which says: Field 'id' expected a number but got 'Ahile Terdoo'. See my Model Code:

class Profile(models.Model):
    customer = models.OneToOneField(User, on_delete=models.CASCADE, null = True)
    surname = models.CharField(max_length=20, null=True)
    othernames = models.CharField(max_length=40, null=True)
    gender = models.CharField(max_length=6, choices=GENDER, blank=True, null=True)
    address = models.CharField(max_length=200, null=True)
    phone = models.CharField(max_length=11, null=True)

    image = models.ImageField(default='avatar.jpg', blank=False, null=False, upload_to ='profile_images', 

)
    def __str__(self):
        return f'{self.customer.username}-Profile'

class Account(models.Model):
    customer = models.OneToOneField(User, on_delete=models.CASCADE, null=True)
    account_number = models.CharField(max_length=10, null=True)
    date = models.DateTimeField(auto_now_add=True, null=True) 

    def __str__(self):
        return f' {self.customer} - Account No: {self.account_number}'

Here is my form:

class SearchCustomerForm(forms.Form):
    value = forms.CharField(label = 'Enter Name or Acct. Number', max_length=30)

Here is my views

def create_account(request): 
    if searchForm.is_valid():
        #Value of search form
        value = searchForm.cleaned_data['value']
        #Filter Customer by Surname, Othernames , Account Number using Q Objects
        user_filter = Q(customer__profile__exact = value) | Q(account_number__exact = value)
        #Apply the Customer Object Filter
        list_customers = Account.objects.filter(user_filter) 
    
    else:
        list_customers = Account.objects.order_by('-date')[:8]

Someone should help on how best to search using Q objects on two Models.

Not sure I properly get your question but if you want to filter Account based on profile surname you need to specify that in your Q object. write your Q object like below

user_filter = Q(customer__profile__surname__exact = value) | Q(account_number__exact = value)

User model's primary key is used as a key in both Account and Profile models.
customer__profile filter on Account model follows the relationship from Account to User to Profile models using this key. This is why it is expecting for customer field to contain a numeric id. __str__ is executed on the python side, and filters are used on the db side, so it does not replace the numeric id with the username.

By convention, a field that defines a relationship to another model should be named after the model in lowercase. So customer should be user, but it is OK to use a different name if you prefer that.

Try filtering by customer__username instead of customer__profile.

To filter by surname, try customer__profile__surname. It should follow the relationship from Account to User using customer field you defined. Then backward from User to Profile by using the lowercase model name (profile).

Back to Top