0

I have a user model with first_name and last_name in it.

class User(AbstractUser):
    first_name = models.CharField(max_length=100)
    last_name = models.CharField(max_length=100)
    address = models.ForeginKey(Address, on_delete=models.CASECAD, related_name='address')
    department = models.CharField(max_length=50)
    ...

And Suppose there are the data in it:

ID   first_name    last_name 
1    FightWith     Code
2    Jon           Doe
3    John          Cena

And another model with Address:

Class Address(models.Model):
    city = models.CharField(max_length=100)
    state = models.CharField(max_length=100)
    country = models.CharField(max_length=100)
    ...

With following data in it:

ID   user    City 
4    1       Ohio
5    2       Delhi
6    3       London

I want to search them based on their fields, these fields like first_name and city/state/country or last_name and city/state/country using Boolean Search technique like following:

John AND (Ohio OR London) or (Jon OR John) AND (Delhi OR London)

Some what similar to those. I am sure we can do this using Q from django.db.models and operator module but I am not sure how.

Has anyone done this and can guide to do the same here then that would be awesome.

FightWithCode
  • 2,190
  • 1
  • 13
  • 24
  • Can you add the relevant parts of the model? – JPG Dec 27 '22 at 06:10
  • Like what? other fields? Let me add some code to demonstrate the mode – FightWithCode Dec 27 '22 at 06:11
  • Just want to see how the `related_name` and `related_query_name` are defined. Apart from that, why there is only *"one"* entry (`John`) for the first example while the second got two (`Jon` & `John`) – JPG Dec 27 '22 at 06:16
  • Added the models. The second query means filter users which has name a Jon or John and belongs to either Delhi or London. I hope that helps. – FightWithCode Dec 27 '22 at 06:20
  • I mean, will the "*number of inputs*" be static or dynamic? For example, will there be a query which searches for `User` has a name of `Jon` or `Doe` or `James` from `Delhi` or `London` or `NY` or `Dubai`? – JPG Dec 27 '22 at 06:24
  • They will be dynamic based on the user search. for lets assume it to be in the simplest form like John OR Jon, John AND Jon, John AND (Ohio OR London) and vice versa – FightWithCode Dec 27 '22 at 06:29

2 Answers2

2

I hope this example would work in your case -

from django.db.models import Q

names = ["John", "Jane", "Joe", "Jill"]
cities = ["New York", "Dubai", "London", "Paris"]

_or_names = Q(_connector=Q.OR, *[Q(first_name=name) for name in names])
_or_city = Q(_connector=Q.OR, *[Q(address__city=city) for city in cities])

filter_exp = _or_names & _or_city

User.objects.filter(filter_exp)
JPG
  • 82,442
  • 19
  • 127
  • 206
1

If you are considering a boolean search and your query can be from multiple field, you can try django full text search queries. https://docs.djangoproject.com/en/4.1/ref/contrib/postgres/search/

Pooja Kumari
  • 86
  • 4
  • It seems a good alternative will surely try this. Thanks. – FightWithCode Dec 27 '22 at 07:03
  • I am trying to use SearchQuery with the User.objects.filter(first_name=SearchQuery()) but getting some SQL error: HINT: "No operator matches the given name and argument types. You might need to add explicit type casts". Could you please tell me if I am missing something here? – FightWithCode Dec 27 '22 at 08:05