Returning more than one table from a query

Using Django Using the ORM
1

Is there a way to return more than one table from a query?

Let’s imagine we have an Organisation model and an OrganisationUser model:

class Organisation(Model):
    name = CharField(max_length=128)

class OrganisationUser(Model):
    organisation = ForeignKey(Organisation, on_delete=CASCADE)
    user = ForeignKey(User, on_delete=CASCADE)
    access = CharField(max_length=1, choices=(('O', 'Owner'), ('W', 'Write'), ('R', 'Read')))

We can get a list of User objects with access to an organisation with User.objects.filter(organisationuser__organisation=obj). Super!

But what if we want to know the access each user has? We want to do something like User.objects.filter(organisationuser__organisation=obj).select_related('organisationuser'), but this doesn’t work. I know, you’re going to say “but select_related doesn’t work on a many-to-many relation”. And I’m going to say “but after the filter, for each row in the result it isn’t a many-to-many, it’s one-to-one”. The database has already found the data, I just want Django to attach it to each returned User object as User.organisationuser.

The closest I’ve managed to get is that you can do annotate(access='organisationuser__access'), which works as far as it goes, inasmuch as it attaches the correct OrganisationUser.access field to each User as User.access, but this is bodgy and ugly and my OrganisationUser is actually more complicated than this so it doesn’t entirely solve my problem.

(Yes I know I can do org.organsationuser_set and then access each User from the objects returned by that, but I don’t want it that way round. I want a list of User objects.)

2

This is very similar to the per-object translation problem where you might have a piece of content that has many translation but is unique per (content, language_code).

It’s likely not going to be of much help as I believe the package might be broken with the recent versions of Django but this is exactly what django-reverse-unique was trying to solve.

Unfortunately select_related is pretty rigid in its current form so there is no way to define these kind of relationships and even less to have select_related introspect filter values to identify a predicate that enforces 1:1 unique constraint safety.

1 Like
3

It almost feels like a bug then that annotate works, given that it’s just assuming that the appropriate value will be returned from the database query and uses it, and that’s what I want select_related to do too - I know the values are available, just use them! But select_related is being too clever and wrongly thinking that the values aren’t there.

4

I’m sorry, I’m a bit confused about what you’re looking for.

Assume the contents of the following models:

  Organization     User
  +-----------+    +-----------+
  |   name    |    |   name    |
  +-----------+    +-----------+
  |  Org A    |    |  Usr 1    |
  |  Org B    |    |  Usr 2    |
  |  Org C    |    |  Usr 3    |
  +-----------+    +-----------+

  OrganizationUser
  +-----------+-----------+--------+
  |   org     |   user    | access |
  +-----------+-----------+--------+
  |  Org A    |  Usr 1    | Owner  |
  |  Org A    |  Usr 2    | Write  |
  |  Org A    |  Usr 3    | Read   |
  |  Org B    |  Usr 1    | Read   |
  |  Org B    |  Usr 2    | Owner  |
  |  Org B    |  Usr 3    | Read   |
  |  Org C    |  Usr 1    | Read   |
  |  Org C    |  Usr 2    | Owner  |
  +-----------+-----------+--------+

What are you looking for as the result set of your query?

Is it the permissions that each user has relative to a specific organization?

Or are you looking to produce a list of all organizations, with a sub list of permissions of users for each?

Or are you looking to produce a list of Users, with a sublist of their permissions to each organization?

5

I want my Organisation model to have a users() method which returns a list of the User objects that have a permission set on this organisation. i.e. I want to do:

SELECT auth_user.*, app_organisationuser.*
  FROM auth_user
  INNER JOIN app_organisationuser ON app_organisationuser.user_id=auth_user.id
  WHERE app_organisationuser.organisation_id=?

and for Django to take that result and give me a list of User objects, and each of those User objects to have an organisationuser attribute which is set to the relevant OrganisationUser object.

Getting Django to do the above query is easy. Getting it to then actually put the data into the appropriate ORM objects appears to be impossible.

6

select_related only works with definition time 1:1 relationships today as the way it deals with creating the associated model instances and JOINs is based off this assumption. Things like calling select_related() without any arguments come to mind.

In the case of annotate there’s no such restriction, you can annotate pretty much anything that can be referenced and it will happily let you create multi-valued JOINs.

If the ORM either supported advanced select_related like it does with prefetch_related such as select_related(Select("organisationuser", Q(organisation=obj)), or allowed for unique together 1:1 to be defined at model definition time it would resolve, or allowed LATERAL JOIN usage to annotate a complete instance with a Subquery (instead of a single field) you could achieve what you’re after but it unfortunately support neither.