使用 LIKE 对未知(动态)数量的查询进行 Rails SQL 查询 [英] Rails SQL query on unknown (dynamic) number of queries using LIKE

问题描述

我有一个执行动态查询的 Rails 搜索表单.也就是说，用户输入如下搜索:

I have a Rails search form that performs a dynamic query. That is, the user enters a search like:

(hobby="skiing") OR (gender="male" AND hobby="jogging") 

所以，我不知道在运行之前我将搜索多少查询.

So, I don't know how many queries I will be searching by until runtime.

我通过将搜索查询转换为有效的 SQL 查询来解析搜索查询，因此上述搜索将转换为以下格式:

I parse the search query by converting it into a valid SQL query, so the above search would be converted to the following format:

query = "hobby LIKE ? OR (gender LIKE ? AND hobby LIKE ?)"
queries = ["skiing", "male", "jogging"]

对于以下查询:

where(query, queries)

但是，Rails 搜索查询的一般语法非常有限:

However, the general syntax of the Rails search query is very limiting:

where(query, query_1, query_2, query_3)

我无法像我想要的那样用数组替换 'query_n' 参数，Rails 不会抛出错误.

I cannot replace the 'query_n' arguments with an array like I want to, without Rails throwing an error.

NoMethodError (undefined method `where' for ["a", "b", "c"]:Array)

尝试splat 数组产生相同的错误:

Attempting to splat the array yields the same error:

where(query, *queries)

再次:

NoMethodError (undefined method `where' for ["a", "b", "c"]:Array)

那我该怎么办?

完整的搜索功能如下所示:

The full search function looks like this:

def self.search(search)
    query = "%#{search}%"
    if search
        includes(:games, :jobs)
        strngs = ["hobby = ? OR name = ? OR gender = ?", "dsfgdsfg", "dsgsdfgsd", "sdfsfsdf"]
        .where(strngs)

推荐答案

您要做的是将数组作为单个参数传递给 where，其中包含查询和动态值.例如:

What you'll want to do is pass an array as a single argument to where which contains both the query AND the dynamic values. For example:

where(["att_1 LIKE ? OR att_2 LIKE ?", "value1", "value2"])

如果数组作为第一个也是唯一的参数传递，则数组的第一个元素被视为模板.以下数组值被视为查询模板的动态值.

If an array is passed as the first and only argument, then the first element of the array is treated as a template. The following array values are treated as the dynamic values for the query template.

对于您的示例，不要将两个单独的变量 queries 和 query 合并为一个 query 变量:

For your example, instead of having two separate variables queries and query, combine them into one query variable:

# A single array with the query AND values
query = ["hobby LIKE ? OR (gender LIKE ? AND hobby LIKE ?)", "skiing", "male", "jogging"]

# Run the `where` with a single array as the argument
YourModel.where(query)

这将允许您使用 LIKE 使用未知数量的值查询数据库.

This will allow you to query the DB with an unknown number of values using LIKE.

参考:Rails where() 文档

这篇关于使用 LIKE 对未知(动态)数量的查询进行 Rails SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！