动态创建查询-Rails 5 [英] Dynamically create query - Rails 5

问题描述

如果我手动编写查询，则类似于

If I manually write a query, it will be like

User.where("name LIKE(?) OR desc LIKE(?)",'abc','abc')
    .where("name LIKE(?) OR desc LIKE(?)",'123','123')

但是，我需要动态生成该查询。
我正在获取数据，例如

However, I need to dynamically generate that query. I am getting data like

def generate_query(the_query)

   query,keywords = the_query

   # Here 
   # query = "name LIKE(?) OR desc LIKE(?)"
   # keywords = [['abc','abc'],['123','123']]

   keywords.each do |keyword|
       users = User.where(query,*keyword) <-- not sure how to dynamically add more 'where' conditions.    
    end

end

我正在使用Rails5。希望很明显。任何帮助表示赞赏：）

I am using Rails 5. Hope it is clear. Any help appreciated :)

推荐答案

类似这样的东西：

q = User.where(a)
        .where(b)
        .where(c)

等同于：

q = User
q = q.where(a)
q = q.where(b)
q = q.where(c)

所以您可以这样写：

users = User
keywords.each do |keyword|
  users = users.where(query, *keyword)
end

但是每当您看到这种反馈模式（即将操作应用于操作结果或 f（f（...（f（x））））时，您都应该开始思考关于 Enumerable＃inject （又称为 Enumerable＃reduce ）：

But any time you see that sort of feedback pattern (i.e. apply an operation to the operation's result or f(f( ... f(x)))) you should start thinking about Enumerable#inject (AKA Enumerable#reduce):

users = keywords.inject(User) { |users, k| users.where(query, *k) }

也就是说，您的查询有两个占位符，但是关键字只是一个平面数组，因此您在以下位置将没有足够的值：

That said, your query has two placeholders but keywords is just a flat array so you won't have enough values in:

users.where(query, *k)

替换占位符。我认为您最好在这里使用命名占位符：

to replace the placeholders. I think you'd be better off using a named placeholder here:

query    = 'name like :k or desc like :k'
keywords = %w[abc 123]
users    = keywords.inject(User) { |users, k| users.where(query, k: k) }

您可能还想添加一些模式匹配您的 Like 这样：

You'd probably also want to include some pattern matching for your LIKE so:

query = "name like '%' || :k || '%' or desc like '%' || :k || '%'"
users = keywords.inject(User) { |users, k| users.where(query, k: k) 

其中 || 是标准的SQL字符串连接运算符（并非所有数据库都支持AFAIK），LIKE模式中的％可以匹配任何字符序列。或者，您可以在Ruby中添加模式匹配，而不必担心数据库处理字符串连接的不同方式：

where || is the standard SQL string concatenation operator (which AFAIK not all databases understand) and % in a LIKE pattern matches any sequence of characters. Or you could add the pattern matching in Ruby and avoid having to worry about the different ways that databases handle string concatenation:

query = 'name like :k or desc like :k'
users = keywords.inject(User) { |users, k| users.where(query, k: "%#{k}%")

---

此外，此：

---

Furthermore, this:

User.where("name LIKE(?) OR desc LIKE(?)",'abc','abc')
    .where("name LIKE(?) OR desc LIKE(?)",'123','123')

生成 WHERE 子句，例如：

where (name like 'abc' or desc like 'abc')
  and (name like '123' or desc like '123')

因此您要匹配所有个关键字，而不是其中的任何个关键字。这可能是您的意图，也可能不是您的意图。

so you're matching all the keywords, not any of them. This may or may not be your intent.

这篇关于动态创建查询-Rails 5的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！