Query over an implicit relationship

Using Django Using the ORM
1

Hello, is there a way to use ORM to query over two models, which both have an email field, but can exist independently?

There are two models:

class User(models.Model):
    id = models.AutoField(primary_key=True)
    email = models.EmailField(unique=True)
    first_name = models.CharField(max_length=50)
    last_name = models.CharField(max_length=50)


class Invitation(models.Model):
    id = models.AutoField(primary_key=True)
    company_id = models.PositiveBigIntegerField()
    email = = models.EmailField()

Each of them can exist without the other.

Is this possible to collect all invitations with user details (if available) is a single SQL?

SELECT i.*, u.* FROM invitations i LEFT OUTER JOIN users u ON i.email = u.email

Thank you.

2

In order to JOIN between two tables the ORM requires that you teach it about the relationship between them as it’s not a query builder but solely a relationship mapper.

In other words, you must make the relationship explicit and today Django only support doing that on the model definition (there’s no way to do it solely at the queryset level).

If both entities can exist without the other you should define a NULL-able ForeignObject between the two

class Invitation(models.Model):
    id = models.AutoField(primary_key=True)
    company_id = models.PositiveBigIntegerField()
    email = models.EmailField()
    user = models.ForeignObject(
        User, models.DO_NOTHING,
        from_fields=["email"], to_fields=["email"],
        null=True,
        related_name="invitations",
    )

then doing

Invitation.objects.select_related("user")

should do what you want.

You can play it here.

2 Likes
3

Just learned something new, nice.

1 Like
4

@charettes I’m curious about ForeignObject . I’ve been using django-relativity to create relationships without creating columns. It has worked well, but struggles with exclude and isn’t keeping up with Django versions these days. It looks like ForeignObject is not an officially supported concept on the Django project, based on the documentation

ForeignObject is an internal API. This means it is not covered by our deprecation policy.

Would you recommend using ForeignObject over django-relativity? With the introduction of composite primary keys, is it likely that ForeignObject will become a supported API in future versions?

5

Unless you need conditional filtering support (that is extra conditions in your relationship definition that should make their way into the JOIN cause) I would personally stick with ForeignObject even if it’s not covered by the deprecation policy (it’s unlikely to change significantly).

In fact relativity.fields.Relation is only a a thin wrapper on top of ForeignObject and if you can live with the awkwardness of using FilteredRelation then you should be able to achieve most of what django-relativity provides (excluding the MPTT stuff) only with core Django. It seems like FilteredRelation works just fine with exclude?

I am of opinion that we need something like Relation in Django core that can be used both as a model field definition or passed directly to QuerySet.alias to deprecate FilteredRelation entirely but it’s hard to do without generic support for CompositeField (as it would allow us to define table returning expressions).

6

Your talk is definitely how I found django-relativity in the first place and it has solved a number of problems for me since! Thanks for the input. Next time we come to it, I might try using ForeignObject and FilteredRelation directly. I think just ForeignObject would solve most, if not all, of my uses of relativity so far.

I agree, Relation would be awesome to have built into Django directly. Having written some simple patches for relativity, I can definitely see how it isn’t as simple as just saying that though.

7

This works perfectly, thank you so much