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: https://airtable.com/appnmIIwVrMFoPOqM/shrZsIF7A8StOHN2Z
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;
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:
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?