Wrong schema is being used when querying cross-schema many_to_many relationships

[gcugnet]

Code of Conduct

• I agree to follow this project's Code of Conduct

AI Policy

• I agree to follow this project's AI Policy, or I agree that AI was not used while creating this issue.

Versions

Component	Version
Elixir	1.19.2
Erlang/OTP	28
Ash	3.24.1
AshPostgres	2.9.0

Operating system

NixOS 25.11

Current Behavior

loading a many_to_many relationship where the join table lives in a different PostgreSQL schema fails with an error like:

** (Postgrex.Error) ERROR 42P01 (undefined_table) relation "catalog.category_notification_templates" does not exist

The query incorrectly uses the source resource's schema (catalog) instead of the join table's schema (notification).

These errors when running tests in my app has been introducted in commit 4ddcffe feat: relationship through (#686),

Reproduction

1. Create resources across different PostgreSQL schemas:

# Profile in "profiles" schema
defmodule MyApp.Profile do
  use Ash.Resource, data_layer: AshPostgres.DataLayer

  postgres do
    table "profile"
    schema "profiles"
    repo MyApp.Repo
  end

  relationships do
    many_to_many :interests, MyApp.Interest do
      through(MyApp.ProfileInterest)
      source_attribute_on_join_resource(:profile_id)
      destination_attribute_on_join_resource(:interest_id)
    end
  end
end

# Interest in "interest" schema
defmodule MyApp.Interest do
  use Ash.Resource, data_layer: AshPostgres.DataLayer

  postgres do
    table "interests"
    schema "interest"
    repo MyApp.Repo
  end
end

# Join table in "profiles" schema
defmodule MyApp.ProfileInterest do
  use Ash.Resource, data_layer: AshPostgres.DataLayer

  postgres do
    table "profile_interests"
    schema "profiles"
    repo MyApp.Repo
  end

  relationships do
    belongs_to :profile, MyApp.Profile, allow_nil?: false
    belongs_to :interest, MyApp.Interest, allow_nil?: false
  end
end

2. Try to load the relationship:

profile = Ash.get!(MyApp.Profile, profile_id)
Ash.load!(profile, :interests)
# => Fails with undefined_table error

Expected Behavior

The query should use the correct schema prefix for the join table (profiles.profile_interests), not the source resource's schema (interest.profile_interests).

Loading cross-schema many_to_many relationships should work correctly:

profile = Ash.get!(MyApp.Profile, profile_id)
Ash.load!(profile, :interests)
# => Returns profile with interests loaded