Dynamic query builder stack too deep for Arel query Rails

user_7123913 0
Viewed 170

I am looking for a way to construct a big dynamic query in Rails and applying an update to the given ActiveRecord relationship:

For example:

my_big_set = Set.new(big_array)
to_update_relationship = my_big_set.reduce(none) do |query, values_to_check|
                            where('condition = ? and condition_two = ?', values_to_check[:first], values_to_check[:two]).or(query)
                         end
    
to_update_relationship.update_all(field_to_update: true)

It works well when to_update_relationship is not too big. But if it gets bigger then the reduce output triggers a SystemStackError: stack level too deep error when it constructs the Arel query.

Is there a clever way to workaround it? (other than splitting the input or increasing the ruby system stack size).

Thanks

PS: using rails 6 and ruby 2.7

arel activerecord ruby-on-rails ruby
2 Answers

my solution finally is:

my_big_set = Set.new(big_array)
subquery = '(condition = ? and condition_two = ?)'
query = Array.new(my_big_set, subquery).join(' OR ')
query_values = my_big_set.map do |values_to_check|
   [values_to_check[:first], values_to_check[:two]]
end.flatten

where(query, *query_values).update_all(field_to_update: true)

that way, we construct:

  1. the SQL query
  2. the values to pass to where()
  3. we still use active record where() in order to be protected from injection etc...

And this fixes the limit!

user_71239130

you could use aggregate functions (for instance, postgresql STRING_AGG or mysql GROUP_CONCAT) to collect pairs of [condition, condition_two] then check those pairs IN your require conditions.

assume that you use postgresql, and conditions is something like [{first: 1, two: 2}, {}, ...]

pair_conditions = conditions.map {|pair| pair.values.join(",")} # ["1,2", ...]
Solution.group(:id, :condition, :condition_two)
.having("STRING_AGG(condition::TEXT || ',' || condition_two::TEXT, '') 
         IN (?)", pair_conditions)

the above query will group (condition, condition_two) and concat them to strings with format "condition,condition_two" so they be able to compare with pre-defined conditions on having clause.

user_122196790
Related