FilteredRelation equivalent from django or multiple columns left join

I try to make a two columns left join with tortoise but I don't find any solution.

The closest query I could achieve is a new condition in the where clause.

Here are my models :

class Roles(Model):
    id = fields.UUIDField(pk=True)
    name = fields.TextField()

    class Meta:
        table = "roles"

class Universes(Model):
    id = fields.UUIDField(pk=True)
    name = fields.TextField(null=False)

    class Meta:
        table = "universes"

class Users(Model):
    id = fields.UUIDField(pk=True)
    universe = fields.ForeignKeyField(model_name="models.Universes")

    class Meta:
        table = "users"

class UserRoles(Model):
    universe = fields.ForeignKeyField(model_name="models.Universes")
    user = fields.ForeignKeyField(model_name="models.Users")
    role = fields.ForeignKeyField(model_name="models.Roles")

    class Meta:
        table = "user_roles"

    @classmethod
    def get_table_name(cls):
        return cls.Meta.table

I want to get these results :

[{'role__name': 'expert', 'user_id': UUID('...')}, {'role__name': 'admin', 'user_id': UUID('...')}]

The SQL query I try to reproduce is :

SELECT "user_roles__role"."name" "role__name", "user_roles"."user_id" "user_id"
FROM "user_roles"
         LEFT OUTER JOIN "roles" "user_roles__role" ON "user_roles__role"."id" = "user_roles"."role_id"
         LEFT OUTER JOIN "users" "user_roles__user" ON "user_roles__user"."id" = "user_roles"."user_id" AND 
          "user_roles__user"."universe_id" = "user_roles"."universe_id"
WHERE "user_roles__user"."language" = 'FR'

The closest SQL query I could get is :

SELECT "user_roles__role"."name" "role__name", "user_roles"."user_id" "user_id"
FROM "user_roles"
         LEFT OUTER JOIN "roles" "user_roles__role" ON "user_roles__role"."id" = "user_roles"."role_id"
         LEFT OUTER JOIN "users" "user_roles__user" ON "user_roles__user"."id" = "user_roles"."user_id"
WHERE "user_roles__user"."universe_id" = "user_roles"."universe_id"
  AND "user_roles__user"."language" = 'FR'

with this python code :

await UserRoles.filter(user__universe=F("universe_id", table=UserRoles)).select_related("user", "role", "universe").filter(user__language="FR").values("role__name", "user_id")

But in a left outer join, the where clause is used after the join and the performance can be very different.

Is there a way to make a left join on two columns ?

FROM table1
LEFT JOIN table2 ON table2.id = table1.user_id AND table2.universe_id = table1.universe_id

I never used django but it seems to have a FilteredRelation class which can be useful.

Start with the UserRoles.filter(...).select_related(...).values(...) query and compare its generated SQL with the two examples in the issue. Trace the related-query and join construction entry points, then verify that a two-column relationship condition can be represented in the LEFT JOIN ON clause without moving it into WHERE.