Django: union of different queryset on the same model

Question

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.

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
– Jose Cherian, Commented Aug 9, 2017 at 1:50

Community · Accepted Answer · 2017-05-23 12:18:13Z

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?

edited May 23, 2017 at 12:18

CommunityBot

11 silver badge

answered Jul 21, 2010 at 15:11

gruszczy

42.4k31 gold badges137 silver badges186 bronze badges

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

1 Comment

Giovanni Di Milia Over a year ago

the other solution is perfect!

Matthew Schinckel · Accepted Answer · 2016-07-04 01:39:51Z

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).

edited Jul 4, 2016 at 1:39

answered Jan 9, 2012 at 10:56

Matthew Schinckel

35.7k6 gold badges91 silver badges122 bronze badges

4 Comments

Dejell Over a year ago

by using Q() objects will the result be duplicated?

Matthew Schinckel Over a year ago

That line of code is rather long, but there is a .distinct() at the end.

rgammans Over a year ago

Upvoting for the use of distinct. The answer using itertools on the other hand implements SQL 'UNION ALL'.

Shadow Over a year ago

This is the best answer here - but it should noted that you can't do this with different django models.

Collectives™ on Stack Overflow

Django: union of different queryset on the same model

2 Answers 2

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related