Suppose I have two models:
class Team(models.Model):
name = models.CharField()
class Player(models.Model):
name = models.CharField()
team = models.ForeignKey(Team)
Now, I want to filter Player objects through their name as well as the name of their team. So for example if user input is aa, I want all Players who have aa in their name OR in their team's name.
So far I have tried to use two queries like this:
players = Player.objects.filter(
name__icontains=request.GET.get("q", ""),
# ...other filters
)
more = Player.objects.filter(
team__name__icontains=request.GET.get("q", ""),
# ...other filters
)
players = players.union(more)
This gives the results, but it has its own problems:
i) It gives duplicate results in cases where the search query matches both, the player name and the team name (.union method does not support .distinct)
ii) It searches the database twice which can be an expensive operation. So I would like it to run in just one query.
iii) It is not DRY. You have to repeat the other filters twice.
So is there a way to get the results accurately?