Finding matching objects from two models using the ORM

Using Django Using the ORM
1

I want to search across two models for objects that have matching names and display a list of them. I can’t figure out if there’s a way to do this with the ORM.

As a very simplified example, say I have these models:

class Company(models.Model):
    name = models.CharField(max_length=255, blank=False)

class Person(models.Model):
    name = models.CharField(max_length=255, blank=False)

I could write SQL to get a list of matching rows something like this:

SELECT
  appname_company.id,
  appname_company.name,
  appname_person.id,
  appname_person.name,
FROM person
LEFT JOIN company ON appname_company.name = appname_person.name;

But in a Django view, if I want to render a table of these matching Companies and Persons, one pair per line, I’m not sure if there’s a good way to do that using the ORM?

2

Directly, no.

Indirectly, it depends upon whether there are multiple entries with the same name in Company. If name is unique in each model, then you can get the values needed by using a subquery using the OuterRef function to perform the comparison between the two models.

If there are duplicate names, it can still be done, but requires more work to construct the related data as a JSONField being annotated to the base object.

Side note: One of the principles of the Django ORM is that models are related by Foreign Keys. I understand that if you’re referencing existing data, this type of query may be necessary due to limitations of the original data. However, if this is a migration of an existing system or a new design, then the appropriate solution is to design your models to work with the ORM - and that means creating either a Foreign Key field or defining a ManyToMany relationship between these models.

3

Thanks Ken.

There are multiple Persons and Companies with the same names.

I understand about foreign keys, but these models aren’t directly related. This is part of an admin tool to identify Persons and Companies with identical names - which sometimes indicates they’ve been created as the wrong kind of object. It’s a long story, but there’s no reason to connect these models.

I’m now thinking maybe I should do this raw sql query to get the IDs of the Persons and Companies, then use the ORM to get QuerySets of Persons and Companies with those IDs, and then combine the two QuerySets into a list of tuples - (<Person>, <Company>). So long as I can get the QuerySets in the correct order.

1 Like
4

/edited: to make lists lists instead of querysets

You can do it, relatively easy, with the orm.

First, you get a list of companies that have more than 0 matching persons:

persons_sqs = Person.objects.filter(slug=OuterRef('name')).values('name').annotate(
    count=Count('name')
)

companies_qs = Company.objects.annotate(
    persons_count=Subquery(persons_sqs.values('count'))
).filter(
    persons_count__gt=0
)

companies = list(companies_qs)  # here you could do pagination

This will return something like this:

[
  {'name': 'A', 'persons_count': 3, ...more company fields},
  {'name': 'A', 'persons_count': 3, ...more company fields},
  {'name': 'B', 'persons_count': 2, ...more company fields},
  {'name': 'C', 'persons_count': 1, ...more company fields},
  ...
]

(A is there twice because I added 2 companies with the name A).

You extract a set of names

companies_names = set([c.name for c in companies])

You then query the persons table for all the persons with the name in the above set and create a dictionary:

persons = {
  p.name: p for p in Person.objects.filter(name__in=companies_names )
}

This results in something like:

{
  'A': Person(...),
  'B': Person(...),
  'C': Person(...),
  ...
}

You now have a companies list and a persons mapping and can easily render them.

You might also want to paginate the companies_qs query before you extract the names set (so you don’t end up with a humonguous persons query.