How to Filter Directly on Relationships Joining a Multitenant Resource to a Non-Multitenant Resource?

Questions & Help Questions
, ,
1

I am facing an issue with filtering across relationships involving multitenant and non-multitenant resources. Here’s my setup:

  1. User
  2. Group : related to User via UserGroup
  3. Permission: related to Group via GroupPermission

All resources are multitenant except User.

Loading related resources is working well but direct filter is not working.

Direct filter uses public schema instead of tenant schema. For the optimization purpose, I want to query DB directly without having to load all data in memory first.

This Does Not Work

   require Ash.Query

   User
    |> Ash.Query.filter(id == ^user.id)
    |> Ash.Query.filter(groups.permissions.name == ^action_type)
    |> Ash.Query.filter(groups.permissions.resource == ^resource)
    |> Ash.exists?(tenant: "company_1", authorize?: false)

It throws (Postgrex.Error) ERROR 42P01 (undefined_table) relation "public.user_groups" does not exist because it is not using the tenant user_groups table.

But This Works

Loading relationship works well.

  Ash.read(User, load: [groups: :permissions], tenant: "company_1", authorize?: false)
2

This is a bug. Please open an issue.

3

I have opened a bug on github: Direct Filtering with Relationships Fails to Respect Tenant Schema When Joining Multitenant and Non Multitenant Resources · Issue #1726 · ash-project/ash · GitHub

4

Also make sure this bug still occurs on the latest version of ash, ash_postgres and ash_sql. I will look into it soon.

5

Thank you for looking into it. The latest versions throws the same error.

6

Reproduced and tested in ash_postgres, fix released in 0.2.47 of ash_sql

7

Thank you @zachdaniel. The issue has been resolved.