Error when reading many_to_many relationship

I have a many_to_many relationship across Domains Stores and Accounts between Store and Employee.

The Join Rescue is in the Stores Domain.

Here is the definition of the Store → Employee many_to_many

    has_many :employees_join_assoc, PointOfSale.Stores.StoreEmployee, public?: true

    many_to_many :employees, PointOfSale.Accounts.Employee do
      through PointOfSale.Stores.StoreEmployee
      domain PointOfSale.Accounts
      source_attribute_on_join_resource :store_id
      destination_attribute_on_join_resource :employee_id
      public? true
    end

When trying to read the relationship via GraphQL I get the following error:

** (FunctionClauseError) no function clause matching in anonymous fn/2 in Ash.Actions.Read.Relationships.attach_related_records/2

    (ash 3.0.10) lib/ash/actions/read/relationships.ex:29: anonymous fn({:__exception__, %Postgrex.Error{
        message: nil,
        postgres: %{
            code: :undefined_table,
            line: "1452",
            message: "relation \"stores\" does not exist",
            position: "39",
            file: "parse_relation.c",
            unknown: "ERROR",
            severity: "ERROR",
            pg_code: "42P01",
            routine: "parserOpenTable"
        },
        connection_id: 46035,
        query: "SELECT DISTINCT s0.\"id\", s1.\"id\" FROM \"stores\" AS s0 INNER JOIN LATERAL (SELECT se0.\"id\" AS \"id\" FROM \"dreambean\".\"employees\" AS se0 INNER JOIN \"dreambean\".\"store_employees\" AS ss1 ON ss1.\"employee_id\" = se0.\"id\" WHERE (ss1.\"store_id\" = s0.\"id\")) AS s1 ON TRUE WHERE (s0.\"id\" = ANY($1))"
    }, [

The join resource:

  relationships do
    belongs_to :store, PointOfSale.Stores.Store do
      primary_key? true
      allow_nil? false
      public? true
    end

    belongs_to :employee, PointOfSale.Accounts.Employee do
      domain PointOfSale.Accounts
      primary_key? true
      allow_nil? false
      public? true
    end
  end

It isn’t limited to Ash GraphQL:

Ash.get!(Store, "34e45990-b109-4bd3-ab97-da4366af1be0", tenant: "dreambean", load: [:employees])

[debug] QUERY OK source="stores" db=2.3ms idle=1844.1ms
SELECT s0."id", s0."name", s0."created_at", s0."updated_at" 
FROM "dreambean"."stores" AS s0 
WHERE (s0."id"::uuid = $1::uuid) 
LIMIT $2 
["34e45990-b109-4bd3-ab97-da4366af1be0", 2]
↳ anonymous fn/3 in AshPostgres.DataLayer.run_query/2, at: lib/data_layer.ex:750

[debug] QUERY ERROR source=“stores” db=0.0ms queue=2.6ms idle=1847.8ms

SELECT DISTINCT s0."id", s1."id", s1."first_name", s1."last_name", s1."auth_code", s1."created_at", s1."updated_at", s1."user_id" 
FROM "stores" AS s0 
INNER JOIN LATERAL (
    SELECT se0."id" AS "id", se0."first_name" AS "first_name", se0."last_name" AS "last_name", se0."auth_code" AS "auth_code", se0."created_at" AS "created_at", se0."updated_at" AS "updated_at", se0."user_id" AS "user_id" 
    FROM "dreambean"."employees" AS se0 
    INNER JOIN "dreambean"."store_employees" AS ss1 ON ss1."employee_id" = se0."id" 
    WHERE (ss1."store_id" = s0."id")
) AS s1 ON TRUE 
WHERE (s0."id" = ANY($1)) 
[["34e45990-b109-4bd3-ab97-da4366af1be0"]]

↳ AshPostgres.DataLayer.run_query_with_lateral_join/4, at: lib/data_layer.ex:955

** (Postgrex.Error) ERROR 42P01 (undefined_table) relation “stores” does not exist

query: 
    SELECT DISTINCT s0."id", s1."id", s1."first_name", s1."last_name", s1."auth_code", s1."created_at", s1."updated_at", s1."user_id" 
    FROM "stores" AS s0 
    INNER JOIN LATERAL (
        SELECT se0."id" AS "id", se0."first_name" AS "first_name", se0."last_name" AS "last_name", se0."auth_code" AS "auth_code", se0."created_at" AS "created_at", se0."updated_at" AS "updated_at", se0."user_id" AS "user_id" 
        FROM "dreambean"."employees" AS se0 
        INNER JOIN "dreambean"."store_employees" AS ss1 ON ss1."employee_id" = se0."id" 
        WHERE (ss1."store_id" = s0."id")
    ) AS s1 ON TRUE 
    WHERE (s0."id" = ANY($1))

(ecto_sql 3.11.2) lib/ecto/adapters/sql.ex:1054: Ecto.Adapters.SQL.raise_sql_call_error/1
(ecto_sql 3.11.2) lib/ecto/adapters/sql.ex:952: Ecto.Adapters.SQL.execute/6
(ecto 3.11.2) lib/ecto/repo/queryable.ex:232: Ecto.Repo.Queryable.execute/4
(ecto 3.11.2) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
(ash_postgres 2.0.8) lib/data_layer.ex:955: AshPostgres.DataLayer.run_query_with_lateral_join/4
(ash 3.0.10) lib/ash/actions/read/read.ex:2389: Ash.Actions.Read.run_query/4
(ash 3.0.10) lib/ash/actions/read/read.ex:446: anonymous fn/5 in Ash.Actions.Read.do_read/4
(ash 3.0.10) lib/ash/process_helpers.ex:38: anonymous fn/4 in Ash.ProcessHelpers.async/2
(elixir 1.16.1) lib/task/supervised.ex:101: Task.Supervised.invoke_mfa/2
(elixir 1.16.1) lib/task/supervised.ex:36: Task.Supervised.reply/4
(ash 3.0.10) lib/ash/process_helpers.ex:32: Ash.ProcessHelpers.async/2
(ash 3.0.10) lib/ash/process_helpers.ex:68: Ash.ProcessHelpers.task_with_timeout/5
(ash 3.0.10) lib/ash/actions/read/read.ex:776: Ash.Actions.Read.maybe_in_transaction/3
(ash 3.0.10) lib/ash/actions/read/read.ex:246: Ash.Actions.Read.do_run/3
(ash 3.0.10) lib/ash/actions/read/read.ex:66: anonymous fn/3 in Ash.Actions.Read.run/3
(ash 3.0.10) lib/ash/actions/read/read.ex:65: Ash.Actions.Read.run/3
(ash 3.0.10) lib/ash/actions/read/relationships.ex:377: anonymous fn/2 in Ash.Actions.Read.Relationships.do_fetch_related_records/3
(elixir 1.16.1) lib/enum.ex:1708: anonymous fn/3 in Enum.map/2
(elixir 1.16.1) lib/enum.ex:4399: anonymous fn/3 in Enum.map/2
iex:2: (file)

Without the load the query doesn’t fail. I noticed that in the query with the load that the stores query isn’t using the tenant schema. In contrast:

The query without the load uses: FROM "dreambean"."stores"

Ash.get!(Store, "34e45990-b109-4bd3-ab97-da4366af1be0", tenant: "dreambean")
[debug] QUERY OK source="stores" db=1.1ms idle=1097.3ms
SELECT s0."id", s0."name", s0."created_at", s0."updated_at" FROM "dreambean"."stores" AS s0 WHERE (s0."id"::uuid = $1::uuid) LIMIT $2 ["34e45990-b109-4bd3-ab97-da4366af1be0", 2]
↳ anonymous fn/3 in AshPostgres.DataLayer.run_query/2, at: lib/data_layer.ex:750
#PointOfSale.Stores.Store<
  client_groups: #Ash.NotLoaded<:relationship, field: :client_groups>,
  employees: #Ash.NotLoaded<:relationship, field: :employees>,
  employees_join_assoc: #Ash.NotLoaded<:relationship, field: :employees_join_assoc>,
  attendances: #Ash.NotLoaded<:relationship, field: :attendances>,
  order_types: #Ash.NotLoaded<:relationship, field: :order_types>,
  prices: #Ash.NotLoaded<:relationship, field: :prices>,
  menus: #Ash.NotLoaded<:relationship, field: :menus>,
  __meta__: #Ecto.Schema.Metadata<:loaded, "dreambean", "stores">,
  id: "34e45990-b109-4bd3-ab97-da4366af1be0",
  name: "Provo Center",
  created_at: ~U[2024-06-07 17:39:10.083677Z],
  updated_at: ~U[2024-06-07 17:39:24.071216Z],
  aggregates: %{},
  calculations: %{},
  ...
>

I put Employee into the Stores domain to rule that out, same error

Hmm, it looks like the schema in the table reference is missing when doing the load. Can you please open an issue on GitHub for this?

@BryanJBryce do all three resources have context(schema) based multi tenancy configured? I’m trying to reproduce this but having trouble.

nvm I got it reproduced :slight_smile:

Fixed in ash_postgres main.

1 Like