I am playing with using Ash-over-Airtable, as a way to progressively go from exploration to prod.

The toy example has student and subject tables, which are associated many-to-many. (One student take many subjects, one subject is enrolled by many students.) The base is public here: Airtable - Wizzle (ash tutorial)

The schema/data is defined in Airtable, which is sync’ed to postgres (using Sequin’s service). The sync respects Airtable’s schema as the source of truth, which means I cannot create a new postgres join-table.

What I tried to do is add a VIEW:

CREATE VIEW student_subject AS 
  SELECT 
    s.id AS student_id, 
    unnest(s.subject_id) AS subject_id 
  FROM student s;

image1200×541 32.8 KB

And subsequently define the join resource over this:

defmodule Wizzle.StudentSubject do
  use Ash.Resource,
    data_layer: AshPostgres.DataLayer

  postgres do
    table "student_subject"
    repo Wizzle.Repo
  end

  relationships do
    belongs_to :subject, Wizzle.Subject do
      attribute_type :string
      primary_key? true
      allow_nil? false
    end
    belongs_to :student, Wizzle.Student do
      attribute_type :string
      primary_key? true
      allow_nil? false
    end
  end

  actions do
    defaults [:create, :read, :destroy] 
  end
end

The many-to-many relationships in the Student Resource (with a mirrored definition in Subject):

    many_to_many :subject, Wizzle.Subject do
      through Wizzle.StudentSubject
      source_attribute :id
      source_attribute_on_join_resource :student
      destination_attribute :subject_id
      destination_attribute_on_join_resource :subject
    end

Loading the relationship generates the sql query

SELECT s0."subject_id", s0."student_id" FROM "student_subject" AS s0 WHERE (ARRAY['rec4s0PKE8yiO0aob','recwI6f4PKujglk13'...

and thus raises:

image1200×724 101 KB

This is in contrast with maybe something like

WHERE (s0."student_id" IN ["rec4s0PKE8yiO0aob",...])

I am not sure if I am missing something / did something wrong, or if Ash Resources cannot be defined over a view table. Suggestions?