Ecto, add raw SQL at end of query or rather choose row at random

Questions / Help
1

Hi

I want to get random row from table using posgresql’s TABLESAMPLE SYSTEM (0.001) LIMIT 1 but I’m really baffled how to do it in an easy way. I wanted to use fragment/1, but to use it query it has to go as a keyword list with one of those:

  @binds    [:where, :select, :distinct, :order_by, :group_by,
             :having, :limit, :offset, :preload, :update]
  @no_binds [:lock]
  @joins    [:join, :inner_join, :left_join, :right_join, :full_join]

Sadly none of this work.

I know i can workaround around this, get for example all ID’s, get on of them at random, and then Repo.get!(that_particular_id), or first geting count of rows, get random number from that range and then something like that select: fragment("*, row_number() OVER () as rnum"), where: fragment("rnum = (?)", chosen_random_number) but all of this needs two queries.

So how can I do it in one query, and get Ecto schema as result?

2 Likes
2

Hi!

Sorry, english is not my native language, what do you mean by “I wanted to use fragment/1, but to use it query it has to go as a keyword list with one of those:”?

@sztosz, have you tried Ecto.Adapters.SQL?

iex(13)> qry = "SELECT * FROM security.roles TABLESAMPLE SYSTEM (100) LIMIT 1"
"SELECT * FROM security.roles TABLESAMPLE SYSTEM (100) LIMIT 1"

iex(14)> Ecto.Adapters.SQL.query!(Repo, qry, [])
[debug] QUERY OK db=2.4ms
SELECT * FROM security.roles TABLESAMPLE SYSTEM (100) LIMIT 1 []
%Postgrex.Result{columns: ["id", "name", "inserted_at", "updated_at"],
 command: :select, connection_id: 98169, num_rows: 1,
 rows: [[1, "admin", {{2017, 1, 19}, {6, 1, 30, 951614}},
   {{2017, 1, 19}, {6, 1, 30, 961019}}]]}
3

I want result to be model struct

query = from c in Case, something: fragment("TABLESAMPLE SYSTEM (0.001) LIMIT 1")

cse_model_struct = Repo.all(query)

But I guess making raw_query and then convert Postgrex.Result to model struct by hand makes sense too. Thanks for pointing me int that direction :slight_smile:

Nevertheless I may be still missing something, and it can be done more easy.

1 Like
4

Ok! BTW, I noticed that you wanted a struct.
I guess we can spy how Ecto makes a struct from query :wink:

5

Oh that should be easy with Enum.zip/2 and Kernel.struct/2 :slight_smile:

2 Likes
6

In Ecto 2.1.0+ there is a Repo.load/2 that will make it a little bit easier. See last example in docs.

2 Likes
7

Great, thank you very much :slight_smile:

8

Thanks @wojtekmach.

For me, and possibly others, it helps to see the full example:

 def new_merchant_transactions(merchant_id, date) do

   sql = """
     SELECT * from transactions
     WHERE merchant_id = $1
   """

  result = Ecto.Adapters.SQL.query!(MyApp.Repo, sql, [merchant_id])
  Enum.map(result.rows, &MyApp.Repo.load(Transaction, {result.columns, &1}))
end

should return something like:

[debug] QUERY OK db=1.1ms
  SELECT * from transactions
  WHERE merchant_id = $1
 [1]
[%Transaction{__meta__: #Ecto.Schema.Metadata<:loaded, "transactions">,
  amount: %Money{amount: 1300, currency: :USD}, id: 70,
  inserted_at: ~N[2018-01-09 16:53:48.964092],
  merchant: #Ecto.Association.NotLoaded<association :merchant is not loaded>,
  merchant_id: 1, reference_number: nil,
  reward: #Ecto.Association.NotLoaded<association :reward is not loaded>,
  reward_id: nil, type: "debit", updated_at: ~N[2018-01-09 16:53:48.964099],
  user: #Ecto.Association.NotLoaded<association :user is not loaded>,
  user_id: 45},
 %Transaction{__meta__: #Ecto.Schema.Metadata<:loaded, "transactions">,
  amount: %Money{amount: 1000, currency: :USD}, id: 69,
  inserted_at: ~N[2017-11-12 22:30:03.972531],
  merchant: #Ecto.Association.NotLoaded<association :merchant is not loaded>,
  merchant_id: 1, reference_number: nil,
  reward: #Ecto.Association.NotLoaded<association :reward is not loaded>,
  reward_id: 3, type: "credit", updated_at: ~N[2017-11-12 22:30:03.972537],
  user: #Ecto.Association.NotLoaded<association :user is not loaded>,
  user_id: 13},
 %Transaction{__meta__: #Ecto.Schema.Metadata<:loaded, "transactions">,
  amount: %Money{amount: 1624, currency: :USD}, id: 68,
  inserted_at: ~N[2017-10-15 01:46:18.689442],
  merchant: #Ecto.Association.NotLoaded<association :merchant is not loaded>,
  merchant_id: 1, reference_number: nil,
  reward: #Ecto.Association.NotLoaded<association :reward is not loaded>,
  reward_id: nil, type: "debit", updated_at: ~N[2017-10-15 01:46:18.689449],
  user: #Ecto.Association.NotLoaded<association :user is not loaded>,
  user_id: 30},

Converting the %Postgrex.Result{ to the right struct.

%Postgrex.Result{columns: ["id", "amount", "type", "user_id", "merchant_id",
  "inserted_at", "updated_at", "reward_id", "reference_number"],
 command: :select, connection_id: 26202, num_rows: 1,
 rows: [[70, 1300, "debit", 45, 1, {{2018, 1, 9}, {16, 53, 48, 964092}},
   {{2018, 1, 9}, {16, 53, 48, 964099}}, nil, nil]]}
2 Likes
9

Hi! I have the same issue, except converting raw sql to Ecto query. is it possible? I wanted to do something like:

Ecto.Queryable.to_query("foo TABLESAMPLE system_rows(1)") ...

update: this answered my question.

1 Like
10

Finally, a real non-hacky solution is here

1 Like