2

I have a custom gem which creates a AR query with input that comes from an elasticsearch instance.

# record_ids: are the returned ids of the ES results
# order: is the order of the of the ids that ES returns

search_class.where(search_class.primary_key => record_ids).order(order)

Right now the implementation is that I build the order string directly into the order variable so it looks like this: ["\"positions\".\"id\" = 'fcdc924a-21da-440e-8d20-eec9a71321a7' DESC"] This works fine but throws a deprecation warning which ultimately will not work in rails6.

DEPRECATION WARNING: Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s): "\"positions\".\"id\" = 'fcdc924a-21da-440e-8d20-eec9a71321a7' DESC". Non-attribute arguments will be disallowed in Rails 6.0. This method should not be called with user-provided values, such as request parameters or model attributes. Known-safe values can be passed by wrapping them in Arel.sql()

So I tried couple of different approaches but all of them with no success.


order = ["\"positions\".\"id\" = 'fcdc924a-21da-440e-8d20-eec9a71321a7' DESC"]

# Does not work since order is an array
.order(Arel.sql(order))

# No errors but only returns an ActiveRecord_Relation
# on .inspect it returns `PG::SyntaxError: ERROR:  syntax error at or near "["`
.order(Arel.sql("#{order}"))
# .to_sql: ORDER BY [\"\\\"positions\\\".\\\"id\\\" = 'fcdc924a-21da-440e-8d20-eec9a71321a7' DESC\"]"

order = ['fcdc924a-21da-440e-8d20-eec9a71321a7', ...]

# Won't work since its only for integer values
.order("idx(ARRAY#{order}, #{search_class.primary_key})")
# .to_sql ORDER BY idx(ARRAY[\"fcdc924a-21da-440e-8d20-eec9a71321a7\", ...], id)

# Only returns an ActiveRecord_Relation
# on .inspect it returns `PG::InFailedSqlTransaction: ERROR:`
.order("array_position(ARRAY#{order}, #{search_class.primary_key})")
# .to_sql : ORDER BY array_position(ARRAY[\"fcdc924a-21da-440e-8d20-eec9a71321a7\", ...], id)

I am sort of stuck since rails forces attribute arguments in the future and an has no option to opt out of this. Since the order is a code generated array and I have full control of the values I am curious how I can implement this. Maybe someone had this issue before an give some useful insight or idea?

Denny Mueller
  • 3,505
  • 5
  • 36
  • 67
  • This has been asked before https://stackoverflow.com/questions/48897070/deprecation-warning-dangerous-query-method-random-record-in-activerecord-5. More info https://github.com/rails/rails/issues/32995. – Sebastián Palma Nov 28 '19 at 14:07
  • @SebastianPalma I am aware of the CLOSED github issue which does no contain a solution for this specific issue. Likewise the linked SO question does not yield a solution for this issue. I try to get my specific case working based on possible solutions which are not working as written in the question. – Denny Mueller Nov 28 '19 at 15:10
  • Is there a reason why you're passing the whole order array? Why not `order[0]`? – Sebastián Palma Nov 28 '19 at 15:32
  • Because the array defines the order of which die results from the db should be ordered. `AR.where(id: some_ids)` does not guaranty the order and I want to apply the order from the elastic result. In this example I just used one entry in the array but they can also be many. – Denny Mueller Nov 28 '19 at 15:46
  • Anyway, why don't you just use `Arel.sql(order.join(', '))`? – Sebastián Palma Nov 28 '19 at 15:48
  • 2
    The problem is passing an array to the Active Record `order` method. – Sebastián Palma Nov 28 '19 at 15:49
  • Let me sleep on that, could be that I dug into this the wrong way and lost sight of the most obvious solution :) As a side note: you can pass in an array to `.order`. `order = ["\"positions\".\"id\" = '430a37b5-953e-476c-ad9e-e7a8b9dd8722' DESC", "\"positions\".\"id\" = 'cf989afa-eabe-4bd2-a8db-79d03eb6fe44' DESC"]` will give you with to_sql `ORDER BY \"positions\".\"id\" = '430a37b5-953e-476c-ad9e-e7a8b9dd8722' DESC, \"positions\".\"id\" = 'cf989afa-eabe-4bd2-a8db-79d03eb6fe44' DESC` – Denny Mueller Nov 28 '19 at 16:03

1 Answers1

3

You could try to apply Arel.sql to the elements of the array, that should work, ie

search_class.where(search_class.primary_key => record_ids)
  .order(order.map {|i| i.is_a?(String) ? Arel.sql(i) : i})
spariev
  • 399
  • 2
  • 5