Ash Elixir•2y ago

Arbitrary queries, like Ecto.Query can do?

I'm using AshPostgres. I have requirements that require building a database query dynamically, based on data passed to it, and it involves SQL joins and possibly other SQL features. How can I do this with Ash? To start with, how do I do JOINs?

18 Replies

moxleyOP•2y ago

Also, while I was searching the docs for answers, I found some issues: 1. The docs mention a AshPostgres.Datalayer.Info module, but that module isn't listed in the left side navigation menu, in the "Code" -> "AshPostgres" section with the other AshPostgres modules where I would expect it. 2. There's no explanation for how to use AshPostgres.Datalayer module in that module's documentation: https://ash-hq.org/docs/module/ash_postgres/latest/ashpostgres-datalayer What does from_ecto/1 do? How about to_ecto/1? What argument do they take?

ZachDaniel•2y ago

So, #1 there seems like an accident that should be fixed But #2 you’re not actually supposed to use it directly from_ecto/1 and to_ecto does some simple transforms of records. Specifically Ecto.NotLoaded and Ash.NotLoaded

moxleyOP•2y ago

Turns out I'm using AshPostgres.DataLayer directly afterall, in my ExMachina factory.

ZachDaniel•2y ago

Well, it’s not a huge deal in that context Although ash has Ash.Seed that you could use instead (But still use ex machina if you want)

moxleyOP•2y ago

I have a ton of investment in ExMachina in this project. I'm not against exploring Ash.Seed.

ZachDaniel•2y ago

You can just replace the call to the data layer with Ash.Seed

moxleyOP•2y ago

Okay, cool.

ZachDaniel•2y ago

Not the entire thing you’ve built As for the original question, you can’t actually build arbitrary ecto queries with Ash. You can build queries based on user input And they can be dynamic and do all kinds of useful things But if you want to write sql directly, you’d need to use ecto All of your ash resources are also ecto schemas

moxleyOP•2y ago

Can I convert between an Ecto.Query and Ash.Query? Or something like that?

ZachDaniel•2y ago

You can go from ash query to ecto query But not the other way around What’s the specific use case?

moxleyOP•2y ago

I have an existing module that is a query builder for a particular use case. It uses Ecto.Query. It is a shared module, so I wouldn't want to write the same module twice-- once for Ecto.Query, and the other for Ash.Query. That module is being called from inside one of my Resource actions. I thought maybe I could convert the Ecto.Query into an Ash.Query, but that won't work.

ZachDaniel•2y ago

Do you need to do anything other than filter/sort?

moxleyOP•2y ago

I need joins

ZachDaniel•2y ago

Yeah, that’s fine I mean does it also modify the select statement Or group by

moxleyOP•2y ago

Like here's one of the function clauses in that module:

  def custom_query_query(_base_query, query, %Operation{
        op: :attended_event_with_tag,
        terms: [%ValueTerm{value: value}]
      }) do
    participation_query = GF.Events.participations_query(attended: true)

    event_query = GF.Events.events_query(past: true, published: true, canceled: false)

    from(m in query,
      distinct: m.id,
      left_join: p in ^subquery(participation_query),
      on: [member_id: m.id],
      left_join: et in EventTag,
      on: [event_id: p.event_id],
      left_join: t in Tag,
      on: [id: et.tag_id],
      left_join: e in ^event_query,
      on: [id: p.event_id],
      where: t.value == ^value
    )
  end

  def custom_query_query(_base_query, query, %Operation{
        op: :attended_event_with_tag,
        terms: [%ValueTerm{value: value}]
      }) do
    participation_query = GF.Events.participations_query(attended: true)

    event_query = GF.Events.events_query(past: true, published: true, canceled: false)

    from(m in query,
      distinct: m.id,
      left_join: p in ^subquery(participation_query),
      on: [member_id: m.id],
      left_join: et in EventTag,
      on: [event_id: p.event_id],
      left_join: t in Tag,
      on: [id: et.tag_id],
      left_join: e in ^event_query,
      on: [id: p.event_id],
      where: t.value == ^value
    )
  end

I don't see in group bys in there. Or selects

ZachDaniel•2y ago

Id look into the modify_query option in the read action You can take an argument there or whatever you’d pass to the query builder And then pass in the current ecto query And return the transformed query

moxleyOP•2y ago

Huh, interesting. I'll look into that. Thank you.

ZachDaniel•2y ago

distinct: m.id might cause problems… AshPostgres adds that when necessary and the two might conflict I'd also add that, in general, we support building a query just like that in Ash

Ash.Query.filter(foo.bar.bad.but.value == ^value)

Ash.Query.filter(foo.bar.bad.but.value == ^value)

in your case to do the attended classes you'd define another relationship for example that filters for attended You can also do: exists(foo.bar.baz..., value == ^value) and then you can make those calculations

calculate :attended_event_with_tag, :boolean, expr(exists(participation, attended and member_id == ^arg(:member_id) and exists(event.tag, value == ^arg(:tag)) do
  argument :member_id, :uuid, allow_nil?: false
  argument :tag, :string, allow_nil?: false
end

calculate :attended_event_with_tag, :boolean, expr(exists(participation, attended and member_id == ^arg(:member_id) and exists(event.tag, value == ^arg(:tag)) do
  argument :member_id, :uuid, allow_nil?: false
  argument :tag, :string, allow_nil?: false
end

then you can say Ash.Query.filter(attended_event_with_tag(member_id: member_id, tag: "tag")) for example You can probably do what you want to do by modifying the echo query, just want to show some other options oh, and one other option you can make your action a manual action, take the ash query, turn it into an ecto query w/ Ash.Query.data_layer_query and then pass it to your code and run the query yourself

Gaming

Programming

Arbitrary queries, like Ecto.Query can do?

Did you find this page helpful?