How to merge 2 Django QuerySets in one and make a SELECT DISTINCT

Question

models.py

class SinglePoint(models.Model):
    attributes = models.TextField(blank=True)
    name = models.CharField(max_length=100)
    geom = models.PointField() #Kartenposition
    objects = models.GeoManager()

class Connection(models.Model):
    name = models.CharField(max_length=100)
    #points = models.ManyToManyField(SinglePoint) #OLD
    p1 = models.ForeignKey(SinglePoint, related_name='p1_set') #NEW
    p2 = models.ForeignKey(SinglePoint, related_name='p2_set') #NEW
    obs = models.ManyToManyField(Observation, blank=True)
    conds = models.ManyToManyField(Condition, blank=True)
    objects = models.GeoManager()
  
class Meta:
    order_with_respect_to = 'p1'

In my view.py:

...
p1_points = SinglePoint.objects.filter(name=name)
p2_points = SinglePoint.objects.filter(name=name)
...

Before I switched to ForeignKey, it worked with:

points = SinglePoint.objects.filter(name=name)

How to 'join' these two QuerySets to one QuerySet and make a distinct()?

Thanks!

score 74 · Answer 1 · answered Apr 09 '13 at 07:37

74

It took me a while to find this

all_points = p1_points | p2_points

answered Apr 09 '13 at 07:37

Krystian Cybulski

10,789
12
67
98

What is the name of this '|' operator in python ? – Lukasz Dynowski Jan 20 '17 at 10:59
1

In python, the `|` operator is called a "bitwise or" or a "union set" operator, depending on the context of how it is used: – Krystian Cybulski Jan 23 '17 at 09:29
1

it can also be called "or" operator :) – Karina Klinkevičiūtė Jun 05 '21 at 16:58

score 9 · Answer 2 · answered Jul 14 '09 at 14:54

9

I am not familiar with geodjango, but combining QuerySets into one QuerySet is possible via the Q-Object and Boolean Operators. See http://docs.djangoproject.com/en/dev/topics/db/queries/#complex-lookups-with-q-objects

Example:

Q(p1_points) | Q(p2_points)

I can't help you further, because I am not really sure what you are trying to accomplish.

answered Jul 14 '09 at 14:54

stefanw

10,456
3
36
34

I want to get all SinglePoint instances in my Project without double SinglePoint instances (distinct). It was no problem with a ManyToMany-Field, because then I accessed them with .filter(connection__vektordata__ ...).distinct() and now I have two Filds: .filter(p1_set__vektordata__ ... and .filter(p2_set__vektordata__ ...) – Jul 14 '09 at 16:03
Sorry, I meant "Fields" not "Filds" – Jul 14 '09 at 16:04
results = Q(p1_points) | Q(p2_points) results.children gives me [[, ], [, ]] but I wish to have [, , , ] to call distinct() – Jul 14 '09 at 16:07

score 7 · Answer 3 · edited Jan 22 '23 at 18:36

7

I think Q queries can achieve what you need like this:

points = SinglePoint.objects.filter(
    Q(name=name) |
    Q(name=name)
).distinct()

edited Jan 22 '23 at 18:36

Super Kai - Kazuya Ito

22,221
10
124
129

answered Jun 21 '13 at 12:56

JoshB

742
1
8
12

score 3 · Answer 4 · answered Apr 12 '19 at 13:01

3

p1_points.union(p2_points)

See Docs

answered Apr 12 '19 at 13:01

techkuz

3,608
5
34
62

The problem with `union()` is that it restricts the actions to the resulting queryset. For instance, you can't `.filter()` or `.annotate()` which is a pretty big drawback. Read more about it [in the docs](https://docs.djangoproject.com/en/4.0/ref/models/querysets/#union). – Austin A Jan 20 '22 at 00:20

Super Kai - Kazuya Ito · Answer 5 · 2023-01-22T19:12:22.333

1

You can use "|"(bitwise or) to combine the querysets of the same model and distinct() and values_list() as shown below:

all_points = (p1_points | p2_points).distinct().values_list('name')

And, you can use |= to add the queryset of the same model as shown below:

all_points = p1_points 
all_points |= p2_points
all_points = all_points.distinct().values_list('name')

edited Jan 22 '23 at 19:12

answered Jan 22 '23 at 05:23

Super Kai - Kazuya Ito

22,221
10
124
129

How to merge 2 Django QuerySets in one and make a SELECT DISTINCT

models.py

5 Answers5

Linked