How to Read Views columns that are not matching Resource attributes?

Ash Forum Ash Questions / Help
,
1

How to read VIEW columns from a custom statement that don’t exist as attribute on the resource without throwing a Ash.Error.Query.NoSuchAttribute

defmodule MyApp.Members.Member do
  use Ash.Resource,
    domain: MyApp.Members,
    data_layer: AshPostgres.DataLayer

  postgres do
    table "members"
    repo SocialFund.Repo

    custom_statements do
      statement :view_member_contributions do
        # Migrations up
        up "CREATE VIEW view_member_contributions
              AS SELECT
               members.member_number,
               contributions.amount AS contributed_amount,
               contributions.month AS period
            FROM members LEFT JOIN  contributions
            ON members.id = contributions.member_id;"

        # Migration down
        down "DROP VIEW IF EXISTS view_member_contributions;"
      end
    end
  end

  actions do
    read :contribution_report do
      prepare build(select: [:member_number, :contributed_amount, :period])
      prepare set_context(%{data_layer: %{table: "view_member_contributions"}})
    end
  end

 # Other definitions
end
2

Hi kamaroly,
You can handle columns from a view that aren’t attributes on your resource by either defining virtual attributes marked as read_only: true or creating a custom read action that selects those extra columns. This way, Ash won’t throw a NoSuchAttribute error, but you can still access the data from your view.

1 Like
3

Thanks. Please share an example code.

1 Like
4

@kamaroly why would you not just make them attributes?

5

Attributes will create columns in the DB but views are only for reading. Is there a way to declare attributes without having them created as columns in the DB?

7

I am looking for a way to create an attribute that won’t generate migrations to create a new table in the DB and add that attribute as a column.

8

You can set migration_ignore_attributes in the Postgres block to tell it to ignore fields and not generate migrations for them.

9

Thanks @zachdaniel, It helped but another problem emerged.

I realised the query is always selecting ID but the action didn’t specify ID in its selection. This fails the query.

The action

    read :contribution_report do
      prepare build(select: [:contributed_amount, :period, :membership_no])
      prepare set_context(%{data_layer: %{table: "view_member_contribution_report"}})
    end

The error

iex(9)> Ash.read(MyApp.Members.Member, action: :contribution_report, tenant: "personal_team_1", authorize?: false)
[debug] QUERY ERROR source="view_member_contribution_report" db=0.0ms queue=0.9ms idle=1345.7ms
SELECT v0."id", v0."period", v0."contributed_amount", v0."membership_no" FROM "personal_team_1"."view_member_contribution_report" AS v0 []
↳ anonymous fn/3 in AshPostgres.DataLayer.run_query/2, at: lib/data_layer.ex:835
{:error,
 %Ash.Error.Unknown{
   bread_crumbs: ["Error returned from: SocialFund.Members.Member.contribution_report"], 
   query: "#Query<>", 
   errors: [
     %Ash.Error.Unknown.UnknownError{
       error: "** (Postgrex.Error) ERROR 42703 (undefined_column) column v0.id does not exist\n\n    query: SELECT v0.\"id\", v0.\"period\", v0.\"contributed_amount\", v0.\"membership_no\" FROM \"personal_team_1\".\"view_member_contribution_report\" AS v0",
       field: nil,
       value: nil,
       splode: Ash.Error,
       bread_crumbs: ["Error returned from: SocialFund.Members.Member.contribution_report"],
       vars: [],
       path: [],
       stacktrace: #Splode.Stacktrace<>,
       class: :unknown
     }
   ]
 }}
10

We always select the primary key. Can that not be added to the view?

1 Like
11

It can be added.

I am seeing a limitation when there’s a need to offload aggregations to the DB due to complexity or speed optimisation. Something like below that won’t require id selection.

CREATE VIEW view_contributions_by_member_category AS
SELECT 
   members.category member_category, 
   sum(contributions.amount) AS contributed_amount
FROM members, contributions
WHERE members.id == contributions.member_id
GROUP BY  members.category member_category;

I hope we’ll be able to turn off id selection in the future.

12

Probably not, TBH. It’s used for a whole bunch of things in Ash proper. You can remove the id from your resource entirely if needed, but not de-select it for a resource that has a primary key.

1 Like