NEW FEATURE: support array of pairs in WHERE IN

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

NEW FEATURE: support array of pairs in WHERE IN

Ana María Martínez Gómez
I had expected that:

BsRequestAction.where('(target_project, target_package) IN (?)', [['target_project1','target_package1'], ['target_project2','target_package2']])

produces the following SQL query:

SELECT  `bs_request_actions`.* FROM `bs_request_actions` WHERE ((target_project, target_package) IN (('target_project1','target_package1'), ('target_project2','target_package2')))

being equivalent to:

BsRequestAction.where("(target_project, target_package) IN (('target_project1','target_package1'), ('target_project2','target_package2'))")

Instead, it produces the following error:

Traceback (most recent call last):
       
1: from (irb):510
TypeError (can't quote Array)

This forces me to write code like:

projects_and_packages = [['target_project1','target_package1'], ['target_project2','target_package2']]
query_string
= projects_and_packages.map { |project, package| "(#{project},#{package})" }.join(",")
BsRequestAction.where("(target_project, target_package) IN (#{query_string})")


In addition, the `where` documentation mentions the following:

the first element of the array is treated as a template, and the remaining elements are inserted into the template to generate the condition. Active Record takes care of building the query to avoid injection attacks, and will convert from the ruby type to the database type where needed. Elements are inserted into the string in the order in which they appear.

which makes me think that this case should be supported (meaning that this could also be considered a bug instead of a feature request). There is nothing else (at least that I have seen) regarding not supported Ruby types or the kind of things that are supported.


I would like to suggest that Rails supports arrays of pairs for WHERE IN queries as it is expected and allow to write much nicer code. I think it wouldn't conflict with current supported behaviors. My main concern are the databases which doesn't support pairs. I think for example SQLlite doesn't support it. But IMO in that case it would be enough with an exception or an small trick like:

SELECT  `bs_request_actions`.* FROM `bs_request_actions` WHERE  target_project || '-' || target_package IN ('target_project1-target_package1', 'target_project2-target_package2')



This has first been discussed in a GitHub issue (or more than discussed redirected here): https://github.com/rails/rails/issues/35925

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/rubyonrails-core.
For more options, visit https://groups.google.com/d/optout.