Load data with Raw SQL statement

Ash Forum Ash Questions / Help
,
1

Hi,

Given an AshResource, how do I load data using raw sql statements?

PS. Use case: We have an existing sql load script that has complicated filter conditions what we don’t want to spend time rewriting in Ash Query

Best regards
Terry

2

Are you looking to supply all of the data from the query? i.e “run a query, return list of resources” from that result? Or is it a calculation, like a SQL condition that should be run per-record? (i.e fragments, if you’re familiar with them in ecto.

3

I’m looking to supply all the data from the query. Basically I would like to do something like

users = MyResource.User
|> Ash.Query.... set_sql("SELECT * FROM users WHERE <legacy_complicated_condition>")
|> MyResource.read!

for user <- users, do: something(user)

PS. set_sql is the imaginary function I am looking for. (?)

4

The way that you would implement this would be with a manual action:

read :your_legacy_thing do
  manual fn _ash_query, ecto_query, context -> 
    # my suggestion, in order to get the most bang for your buck, is to use Ecto
    # here, and *join* to the ecto_query we provide.
  end
end

If you’re familiar with Ecto, this should be doable. You can also use YourRepo.query! to run raw SQL, but you will have two major drawbacks of this:

  1. you will lose whatever logic Ash already did to honor the original request
  2. you will need to post-process the results into records (not that hard, typically just `Enum.map(…, struct!(Resource, &1))
1 Like