# Finding matching objects from two models using the ORM

**URL:** <https://forum.djangoproject.com/t/finding-matching-objects-from-two-models-using-the-orm/27901>\
**Category:** Using the ORM\
**Created:** [February 8, 2024, 6:12pm UTC](https://forum.djangoproject.com/t/finding-matching-objects-from-two-models-using-the-orm/27901 "2024-02-08T18:12:39Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![philgyford](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/philgyford/32/14295_2.png) [@philgyford](https://forum.djangoproject.com/u/philgyford)\
**Post date:** [February 8, 2024, 6:12pm UTC](https://forum.djangoproject.com/t/finding-matching-objects-from-two-models-using-the-orm/27901/1 "2024-02-08T18:12:39Z")

</div>

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:

```python
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:

```sql
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?

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [February 8, 2024, 6:52pm UTC](https://forum.djangoproject.com/t/finding-matching-objects-from-two-models-using-the-orm/27901/2 "2024-02-08T18:52:18Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![philgyford](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/philgyford/32/14295_2.png) [@philgyford](https://forum.djangoproject.com/u/philgyford)\
**Post date:** [February 8, 2024, 7:05pm UTC](https://forum.djangoproject.com/t/finding-matching-objects-from-two-models-using-the-orm/27901/3 "2024-02-08T19:05:51Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![coditza](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/coditza/32/18944_2.png) [@coditza](https://forum.djangoproject.com/u/coditza)\
**Post date:** [February 9, 2024, 3:39pm UTC](https://forum.djangoproject.com/t/finding-matching-objects-from-two-models-using-the-orm/27901/4 "2024-02-09T15:39:12Z")

</div>

/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:

```auto
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:

```auto
[
  {'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

```auto
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:

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

```

This results in something like:

```auto
{
  '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.