2

I'm programming a search on a model and I have a problem.

My model is almost like:

class Serials(models.Model):
    id = models.AutoField(primary_key=True)
    code = models.CharField("Code", max_length=50)
    name = models.CharField("Name", max_length=2000)

and I have in the database tuples like these:

1   BOSTON   The new Boston
2   NYT      New York journal
3   NEWTON   The old journal of Mass
4   ANEWVIEW The view of the young people

If I search for the string new, what I want to have is:

  • first the names that start with the string
  • then the codes that start with the string
  • then the names that contain the string
  • then the codes that contain the string

So the previous list should appear in the following way:

2   NYT      New York journal
3   NEWTON   The old journal of Mass
1   BOSTON   The new Boston
4   ANEWVIEW The view of the young people

The only way I found to have this kind of result is to make different searches (if I put "OR" in a single search, I loose the order I want).

My problem is that the code of the template that shows the result is really redundant and honestly very ugly, because I have to repeat the same code for all the 4 different querysets. And the worse thing is that I cannot use the pagination!

Now, since the structure of the different querysets is the same, I'm wandering if there is a way to join the 4 querysets and give the template only one queryset.

Kevin Brown-Silva
  • 40,873
  • 40
  • 203
  • 237
Giovanni Di Milia
  • 13,480
  • 13
  • 55
  • 67
  • Starting from version 1.11, django query sets have a builtin union method. I have added it as an answer for future reference – Jose Cherian Aug 09 '17 at 01:50

2 Answers2

7

You can make those four queries and then chain them inside your program:

result = itertools.chain(qs1, qs2, qs3, qs4)

but this doesn't seem to nice because your have to make for queries.

You can also write your own sql using raw sql, for example:

Serials.objects.raw(sql_string)

Also look at this:

How to combine 2 or more querysets in a Django view?

Community
  • 1
  • 1
gruszczy
  • 40,948
  • 31
  • 128
  • 181
5

You should also be able to do qs1 | qs2 | qs3 | qs4. This will give you duplicates, however.

What you might want to look into is Q() objects:

from django.db.models import Q
value = "new"
Serials.objects.filter(Q(name__startswith=value) |
                       Q(code__startswith=value) |
                       Q(name__contains=value) |
                       Q(code__contains=value).distinct()

I'm not sure if it will handle the ordering if you do it this way, as this would rely on the db doing that.

Indeed, even using qs1 | qs2 may cause the order to be determined by the db. That might be the drawback (and reason why you might need at least two queries).

Matthew Schinckel
  • 35,041
  • 6
  • 86
  • 121