Filter with subquery without fragment?

Ash Forum Ash Questions / Help
1

Can I filter with a subquery without using a fragment so I don’t hard code the table name and column name in the fragment?

  postgres do
    table "entities"
    repo MyApp.Repo
  end

  actions do
    read :read_fragment do
      filter expr(fragment("? in (SELECT name FROM entities WHERE parent_id = '123')", description)
    end

    read :read_partial do
      filter expr(description in fragment("(SELECT name FROM entities WHERE parent_id = '123')")
    end

    # are these possible?
    read :read_subquery do
      filter expr(description in Ash.Query.select(MyApp.Entity, [:name]))
    end

    read :read_subquery do
      filter expr(description in expr(...))
    end
  end
2

you’d make a relationship representing these things, for example:

has_many :foo, Bar do
  no_attributes? true # no attributes means no source/destination attribute

  filter expr(parent(type) == type) # add filters to the relationship `parent/1` means this record
end

and then

read :read_fragment do
  filter expr(exists(foo, parent_id == "123" and description == parent(description)))
end

EDIT: there is an open issue I’d like to tackle soon to support using resources instead of relationships in aggregates like that, i.e exists(Foo, parent_id ...) which would prevent the need for a relationship for these things, but currently its not supported.

3

Can I pass an argument or variable by context to has_many?

I couldn’t find a way to do something like this.

filter expr(description in fragment("(SELECT name FROM entities WHERE parent_id = ?)", ^arg(:parent_id))
4

You can get context, yes, but relationships don’t take arguments (currently). Generally speaking you’d want to do that on-demand.

load(relationship: Ash.Query.filter(..., fragment("...")))
1 Like