Django, having Q object filter by function in model

Question

Inside my Profile model I have the following function:

It serves to return the fullname of the user (or a alternative if some data is missing).

    def full_name(self):
      first_name = self.user.first_name.strip()
      if first_name and self.user.last_name:
          if not self.middle_name:
              full_name = u'%s %s' % (first_name, self.user.last_name)
          else:
              full_name = u'%s %s %s' % (first_name, self.middle_name,
                                       self.user.last_name)
          return full_name.strip()
      elif first_name:
          return first_name
      return self.user.username

Now for my question: I have a view where I filter a queryset based on the variable 'q' (that is returned from a searchbox in the template) Like so:

qs = qs.filter(tenant_demo_purchase=True).order_by('-id')
    #Search users within results
    q = self.request.GET.get('q', None)
    if q:
        qs = qs.filter(Q(user__username__icontains=q) |
                       Q(user__first_name__icontains=q) |
                       Q(user__last_name__icontains=q) |
                       Q(arrangement_period__arrangement__title__icontains=q)  | 
                       ).filter(tenant_demo_purchase=True).order_by('-id')
    else:
        pass
    return qs

This filter works fine if I give it a firstname, username, or a last name. But if I give it a firstname + lastname (example: "John Doe") it returns no results.

Most of my users tend to submit the fullname, so as a solution I tried to have it acces the Profile.full_name function. By adding the following line

Q(user__profile__fullname__icontains=q)

However, this crashes with the following error message:

raise FieldError('Related Field got invalid lookup: {}'.format(lookups[0]))
FieldError: Related Field got invalid lookup: fullname

Interistengly, when I look at the django error page it seems to ignore the usernames and fail on the 'arrangement__title' query, ignoring the rest:

 Q(arrangement_period__arrangement__title__icontains=q)

I tried doing the obvious:

Q(user__profile.fullname__icontains=q)

But that just throws the following error

SyntaxError: keyword can't be an expression

I just want a way for users to input a full name (firstname + space + lastname) and still have the program return the correct results.

Does anyone know of a way to do this?

Solution

Thanks to Ivan his answer, the following codes provides the desired result:

q = self.request.GET.get('q', None)
    if q:
        qs = qs.annotate(
            full_name=Concat(
                'user__first_name',
                Value(' '),
                'user__last_name',
                output_field=CharField()
            )
        ).filter(Q(full_name__icontains=q) |
                 Q(user__username__icontains=q) |
                 Q(user__first_name__icontains=q) |
                 Q(user__last_name__icontains=q) |
                 Q(arrangement_period__arrangement__title__icontains=q)
                 ).order_by('-id')

you are missing an OR operator after the line Q(user__first_name__icontains=q) — alfonso.kim
– alfonso.kim, Commented Oct 11, 2017 at 13:27
Oops, seems it got lost when I was pasting/editing the code for the post. — Jasper
– Jasper, Commented Oct 11, 2017 at 13:31

Ivan · Accepted Answer · 2017-10-11 13:21:28Z

5

Try using database function concat:

from django.db.models import CharField, Value
from django.db.models.functions import Concat


qs = qs.annotate(
    full_name=Concat(
        'user__first_name',
        Value(' '),
        'user__last_name',
        output_field=CharField()
    )
).filter(full_name__icontains=q)

answered Oct 11, 2017 at 13:21

Ivan

2,6751 gold badge14 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jasper Over a year ago

Thank you so much! I was able to use this code to get my program working. I' ll update my question accordingly.

Collectives™ on Stack Overflow

Django, having Q object filter by function in model

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related