Can i use with AR `update_all`

[nimmolo]

Hi, thank you for this library.

Is it possible to use the Arel extensions inside an ActiveRecord update_all statement?
I've tried to use the + concat function like this, but it's throwing a NoMethodError: undefined method

concat_str = "#{string} "
search_str = "% #{string} %"
Name.where(Name[:column].does_not_match(search_str)).
     update_all(column: Name[:column] + concat_str)

[jdelporte]

Hi and thanks for you interest in our library,

First of all ArelExtensions has no power on ActiveRecord, cannot and should not have any.

This issue appears with old versions of ActiveRecord (It appears on 4.2 and older, I tested did not test on 5 or 5.1 but It worked on 5.2, 6.* and 7).
On recent versions it will generate the sql query correctly:

UPDATE `names` SET `users`.`column` = (`names`.`column` + 'foobar ') WHERE `names`.`column` NOT LIKE '% foobar %'

On older versions, you will need to write this inelegantly:

Name.update_all("collumn = #{(Name[:column] + concat_str).to_sql}")

Note that it is not an ArelExtensions issue, ActiveRecord will not comprehend Arel Nodes either. The following does not work:

Name.update_all(column: Arel::Nodes::Addition.new(Name[:column], Arel::Nodes.build_quoted('foobar')))

If you cannot upgrade rails, I guess you will have to deal with this unpleasant writing.

[nimmolo]

Thanks @jdelporte . I'm on Rails 5.2, so it may be generating the SQL correctly, and my problem may be elsewhere.
My ActiveRecord version is 5.2.7.1

I tried all your syntax examples (thank you!), including the inelegant one.
Now I'm getting a slightly less unspecific error that resembles what i get sometimes when the model's arel_table helper is not defined, although it is included in my code.

undefined method '[]' for #<Class:0x00005620c8730138>

I'll come back to this tomorrow and report back.

[nimmolo]

Just tried that hypothesis. Yes, it has nothing to do with Arel extensions, and they do work in this context.

My problem was that my method was being called from another class, and the ArelTable helpers were not included. I had to simply define the arel_table (and use the ugly syntax), and the query was correctly formed. Thank you!

[nimmolo]

Your provided syntax was very helpful, but I'd like to revisit this question since i'm getting inconsistent results.
I'm on Rails 5.2.2.
The following does not work for me, but if I understand you correctly, it works for you on Rails 5.2, Rails 6 and Rails 7:

concat_str = "#{string} "
search_str = "% #{string} %"
Name.where(Name[:column].does_not_match(search_str)).
     update_all(column: Name[:column] + concat_str)

This also does not work:

Name.where(Name[:column].does_not_match(search_str)).
     update_all(column: Name[:column].concat(concat_str))

To get the concat to work, I have to use the inelegant string interpolation you suggested:

Name.where(Name[:column].does_not_match(search_str)).
     update_all("column = #{(Name[:column] + concat_str).to_sql}")

However, the following replace statement does work for me.
I don't understand why replace would work, if the above concat does not?????

replace_str = " #{string} "
search_str  = "% #{string} %"
Name.where(Name[:column].matches(search_str)).
      update_all(column: Name[:column].replace(replace_str, " "))

[jdelporte]

What's your error?
As I said, it is not an ArelExtensions issue but a rails issue.

[nimmolo]

Hi sorry - I posted prematurely. Revising the post currently... will paste in the error in a minute!

[nimmolo]

ah sorry.
When I execute this method in rails console, it seems it actually does execute the SQL correctly -- but when I test the method, my test fails.

Thanks for responding so quickly, i've been coming back to this for days trying to get it to pass the minitests, and quite mystified.
But it didn't occur to me maybe the test is badly written somehow!

[jdelporte]

Don't worry. I hope you will find the problem.

[nimmolo]

Thanks - Actually no - the SQL does not throw an error, but it also does not execute as expected.
Using update_all(column: Name[:column].concat(concat_str)) or update_all(column: Name[:column] + concat_str) the statement stores this in the column
"#<ArelExtensions::Nodes::Concat:0x000055e4d91d3738...
instead of storing the concatenated string.

Obviously your "inelegant" workaround (update_all("column = #{(Name[:column] + concat_str).to_sql}")) does store the string correctly, but i don't understand why the other one does not work.

Is it the case that only later versions of ActiveRecord (>5.2.2) can understand this syntax and correctly parse the Arel Node?

[jdelporte]

Yes, it seems update_all did not support Arel::Nodes::Function as values at some point.
I am quite surprised that replace works and concat does not, they both are Arel::Nodes::Function.

[nimmolo]

Ah. Ok - Thank you!! I kept trying and trying different syntaxes 😟 ... was so confused!