jkwchui

Can Ash define Resource on Postgres View table?

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;

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?

2 comments

/ash #troubleshooting #database

0 644 2

2023-10-17 04:37:49 UTC

Marked As Solved

zachdaniel

Creator of Ash

Ash resources should work just fine on top of a view. This looks like a different kind of issue entirely. Somehow the filter statement is being generated incorrectly. Your setup looks pretty straightforward…oh, hmm…

I think your many to many is configured wrong:

      source_attribute_on_join_resource :student_id
      destination_attribute :id
      destination_attribute_on_join_resource :subject_id

Post #1

Also Liked

jkwchui

Thanks Zach. I haven’t wrapped my head around many_to_many correctly, but I am closer after this.

With this solved, it’s a banging setup:

Start with a spreadsheet-like setup
→ Zapier content into Airtable
→ bi-directional sync to a hosted Postgres
→ Ash declarations in Livebook

Work out the (elixir) APIs interactively, then
→ copy-paste into a new Mix project to test (aside from config, it’s all neatly in a folder)
→ drop into any existing, deployed Phoenix projects

Even without any extra tooling built for this, Ash Resources are so standardized that the it’s just an hour of wiring for someone inexperienced like me , and you get: Airtable “deployed”, receiving custom CRUD from LiveView/Elixir (bumblebee and vega-lite and Extypst and…) using Sequin’s sync (or just split off into the standalone postgres db), and the LiveView gets updates from Airtable, allowing contribution from non-technical content editors. And how the coupling is so loose, these Resources are like a cartridge that can just be plugged in.

I will do some more poking at the seams / ask some questions to the guys at Sequin, and try to get a tutorial out. Given they sync to-from Hubspot and Salesforce and […?] so this could be more generally useful than just someone with dozens of Airtable bases.

Post #2