0

I have the following Django models:

class Person(models.Model):
    name = models.CharField(max_length=50)

class Pet(models.Model):
    name = models.CharField(max_length=50)

class Change(models.Model):
    table_name = models.CharField(max_length=50)
    record_id = models.IntegerField()
    workspace_id = models.IntegerField(null=True)

I can load some data into the database using the following script:

p1, _ = Person.objects.get_or_create(name="Person1")
_p1, _ = Person.objects.get_or_create(name="Person1.DRAFT")
p2, _ = Person.objects.get_or_create(name="Person2")
_p2, _ = Person.objects.get_or_create(name="Person2.DRAFT")
p3, _ = Person.objects.get_or_create(name="Person3")

t1, _ = Pet.objects.get_or_create(name="Pet1")
t2, _ = Pet.objects.get_or_create(name="Pet2")

c1, _ = Change.objects.get_or_create(table_name="Person", record_id=_p1.pk, workspace_id=1)
c2, _ = Change.objects.get_or_create(table_name="Person", record_id=_p2.pk, workspace_id=2)

I can write a query that stacks all Person and Pet rows. That can be easily done using the SQL UNION operator:

SELECT 'Person' as type, t.id, t.name FROM union_person t
UNION
SELECT 'Pet' as type, t.id, t.name FROM union_pet t;

Which generates the following result:

| type   | id  | name          |
| :----- | :-- | :------------ |
| Person | 1   | Person1       |
| Person | 2   | Person1.DRAFT |
| Person | 3   | Person2       |
| Person | 4   | Person2.DRAFT |
| Person | 5   | Person3       |
| Pet    | 1   | Pet1          |
| Pet    | 2   | Pet2          |

Now my goal is to add a workspace_id column via a JOIN with the Change table. The table I want should look like this (which is exactly the same as the table above, just with the workspace_id values added, if they exist):

| type   | id  | name          | workspace_id |
| :----- | :-- | :------------ | :----------- |
| Person | 1   | Person1       | NULL         |
| Person | 2   | Person1.DRAFT | 1            |
| Person | 3   | Person2       | NULL         |
| Person | 4   | Person2.DRAFT | 2            |
| Person | 5   | Person3       | NULL         |
| Pet    | 1   | Pet1          | NULL         |
| Pet    | 2   | Pet2          | NULL         |

This is the query I have to achieve this:

SELECT 'Person' as type, t.id, t.name, c.workspace_id FROM union_person t
LEFT JOIN union_change c on t.id = c.record_id and 'Person' = c.table_name
UNION
SELECT 'Pet' as type, t.id, t.name, c.workspace_id FROM union_pet t
LEFT JOIN union_change c on t.id = c.record_id and 'Pet' = c.table_name;

Is this the best solution or is there a better one out there?

Note I need this query to create a PostgreSQL view, which will be used to search the entire database.

Johnny Metz
  • 5,977
  • 18
  • 82
  • 146
  • That looks a reasonable solution. It looks like you're performing a union elsewhere and I wonder if there may be an advantage in not doing that and performing the outer joins with the underlying data instead. – Richard Crossley Feb 19 '20 at 07:10
  • You can use `UNION ALL` which does not try to remove duplicates (which you can't have) and thus is a bit faster –  Feb 19 '20 at 08:27
  • Does this answer your question? [Can UNION ALL be faster than JOINs or do my JOINs just suck?](https://stackoverflow.com/questions/3374459/can-union-all-be-faster-than-joins-or-do-my-joins-just-suck) – philipxy Feb 19 '20 at 17:30

0 Answers0