I have the following variables:
我有以下几个变量:
query = "(first_name = ?) AND (country = ?)" # string
values = ['Bob', 'USA'] # array
I need the following result:
我需要以下结果:
result = "(first_name = Bob) AND (country = USA)" # string
The number of substitutions varies (1..20).
替换的次数不同(1.. .20)。
What is the best way to do it in Ruby?
在Ruby中最好的方法是什么?
5 个解决方案
#1
3
If you don't mind destroying the array:
如果你不介意破坏数组:
query.gsub('?') { values.shift }
query.gsub(' ? '){值。转变}
Otherwise just copy the array and then do the replacements.
否则就复制数组，然后替换。
Edited: Used gsub('?') instead of a regex.
编辑:使用gsub('?')代替regex。
#2
2
If you can control the query string, the String#% operator is what you need:
如果您可以控制查询字符串，那么您需要的是字符串#%操作符:
query = "(first_name = %s) AND (country = %s)" # string
values = ['Bob', 'USA'] # array
result = query % values
#=> "(first_name = Bob) AND (country = USA)"
You have to replace ? with %s in your query string.
你需要替换吗?在查询字符串中包含%s。
#3
1
values.inject(query){|s1, s2| s1.sub("?", s2)}
#4
1
query = "(first_name = ?) AND (country = ?)"
values = ['Bob', 'USA']
result = query.dup
values.each{|s| result.sub!("?",s) }
p query
# >> "(first_name = Bob) AND (country = USA)"
#5
0
Since this question is tagged ruby-on-rails, you could (should) use ActiveRecord::QueryMethods#where with Array Conditions.
由于这个问题被标记为ruby on-rails，您可以(应该)使用ActiveRecord::QueryMethods#，其中包含数组条件。
This will raise ActiveRecord::PreparedStatementInvalid if you provide the wrong number of bind variables in values, which saves you from checking this yourself.
如果您在值中提供了错误数量的绑定变量，这将导致ActiveRecord: PreparedStatementInvalid，这使您不必亲自检查它。
Not only is this elegant because Rails performs the substitutions for you, it will also help guard you against SQL injection attacks by sanitising your SQL in the process.
这不仅是因为Rails为您执行替换，而且还将通过在过程中清理SQL来帮助您防范SQL注入攻击。
Example
Assuming your model is called User and query and value are as in your question, this:
假设您的模型名为User，查询和值如您的问题所示，则如下:
User.where(query, *values)
...would generate SQL like:
…生成SQL想:
SELECT * FROM users WHERE first_name = 'Bob' AND country = 'USA';
Display only
The example above will execute the query. To display the generated SQL without execution, simply invoke ActiveRecord::Relation#to_sql:
上面的示例将执行查询。要显示生成的SQL而不执行，只需调用ActiveRecord:: relationship #to_sql:
User.where(query, *values).to_sql
#1
3
If you don't mind destroying the array:
如果你不介意破坏数组:
query.gsub('?') { values.shift }
query.gsub(' ? '){值。转变}
Otherwise just copy the array and then do the replacements.
否则就复制数组，然后替换。
Edited: Used gsub('?') instead of a regex.
编辑:使用gsub('?')代替regex。
#2
2
If you can control the query string, the String#% operator is what you need:
如果您可以控制查询字符串，那么您需要的是字符串#%操作符:
query = "(first_name = %s) AND (country = %s)" # string
values = ['Bob', 'USA'] # array
result = query % values
#=> "(first_name = Bob) AND (country = USA)"
You have to replace ? with %s in your query string.
你需要替换吗?在查询字符串中包含%s。
#3
1
values.inject(query){|s1, s2| s1.sub("?", s2)}
#4
1
query = "(first_name = ?) AND (country = ?)"
values = ['Bob', 'USA']
result = query.dup
values.each{|s| result.sub!("?",s) }
p query
# >> "(first_name = Bob) AND (country = USA)"
#5
0
Since this question is tagged ruby-on-rails, you could (should) use ActiveRecord::QueryMethods#where with Array Conditions.
由于这个问题被标记为ruby on-rails，您可以(应该)使用ActiveRecord::QueryMethods#，其中包含数组条件。
This will raise ActiveRecord::PreparedStatementInvalid if you provide the wrong number of bind variables in values, which saves you from checking this yourself.
如果您在值中提供了错误数量的绑定变量，这将导致ActiveRecord: PreparedStatementInvalid，这使您不必亲自检查它。
Not only is this elegant because Rails performs the substitutions for you, it will also help guard you against SQL injection attacks by sanitising your SQL in the process.
这不仅是因为Rails为您执行替换，而且还将通过在过程中清理SQL来帮助您防范SQL注入攻击。
Example
Assuming your model is called User and query and value are as in your question, this:
假设您的模型名为User，查询和值如您的问题所示，则如下:
User.where(query, *values)
...would generate SQL like:
…生成SQL想:
SELECT * FROM users WHERE first_name = 'Bob' AND country = 'USA';
Display only
The example above will execute the query. To display the generated SQL without execution, simply invoke ActiveRecord::Relation#to_sql:
上面的示例将执行查询。要显示生成的SQL而不执行，只需调用ActiveRecord:: relationship #to_sql:
User.where(query, *values).to_sql