在 Rails 中动态构建查询 [英] Building queries dynamically in rails

问题描述

我正在尝试使用 ruby​​ on rails 复制 crunchbase 的搜索列表样式.我有一组看起来像这样的过滤器:

<预><代码>[{"id":"0","className":"公司","字段":"名称","operator":"开始于","val":"a"},{"id":"1","className":"公司","field":"hq_city","运算符":"等于","val":"卡拉奇"},{"id":"2","className":"类别","字段":"名称","operator":"不包括","val":"电子商务"}]

我将此 json 字符串发送到我已实现此逻辑的 ruby​​ 控制器:

filters = params[:q]表名 = {}过滤器.每个做|过滤器|过滤器 = 过滤器[过滤器]类名=过滤器[类名"]字段名称 = 过滤器[字段"]运算符 = 过滤器[运算符"]val = 过滤器 ["val"]如果 table_names[className].blank?table_names[className] = []结尾table_names[className].push({字段名称:字段名称，运营商:运营商，价值:价值})结尾table_names.each 做 |k, v|我 = 0where_string = ''val_hash = {}v.各自做|领域|如果我＞0where_string += ' AND '结尾where_string += "#{field[:fieldName]} = :#{field[:fieldName]}"val_hash[field[:fieldName].to_sym] = field[:val]我 += 1结尾类名 = k.constantizeputs className.where(where_string, val_hash)结尾

我所做的是，我遍历 json 数组并创建一个带有键作为表名的散列，值是具有列名、运算符和应用该运算符的值的数组.所以在创建 table_names 哈希后我会有这样的事情:

<代码>{'公司':[{字段名称:'名称'，运营商:'开始于'，val:'a'},{fieldName:'hq_city',运算符:'等于'，val:'卡拉奇'}],'类别':[{字段名称:'名称'，运算符:'不包括'，val:'电子商务'}]}

现在我遍历 table_names 哈希并使用 Model.where("column_name = :column_name", {column_name: 'abcd'}) 语法创建一个 where 查询.

所以我会生成两个查询:

SELECT "companies".* FROM "companies" WHERE (name = 'a' AND hq_city = 'b')SELECT "categories".* FROM "categories" WHERE (name = 'c')

我现在有两个问题:

1.运营商:

我有许多运算符可以应用于诸如开始于"、结束于"、等于"、不等于"、包含"、不包括"、大于"等列，'少于'.我猜最好的方法是在操作符上做一个 switch case 并在构建 where 字符串时使用适当的符号.因此，例如，如果运算符是以"开头，我会做类似 where_string += "#{field[:fieldName]} 之类的事情 %:#{field[:fieldName]}"对其他人也是如此.

那么这种方法是否正确，这种类型的.where中是否允许这种类型的通配符语法?

2.超过 1 张桌子

如您所见，我的方法为 2 个以上的表构建了 2 个查询.我不需要 2 个查询，我需要类别名称在该类别属于公司的同一查询中.

现在我想做的是我需要创建一个这样的查询:

Company.joins(:categories).where("name = :name and hq_city = :hq_city and Categories.name = :categories[name]", {name: 'a', hq_city: 'Karachi'，类别:{名称:'电子商务'}})

但事实并非如此.搜索会变得非常复杂.例如:

一家公司有很多 FundingRound.FundingRound 可以有很多 Investment，Investment 可以有很多 IndividualInvestor.所以我可以选择创建一个过滤器，如:

<代码>{"id":"0","className":"个人投资者","field":"first_name","operator":"开始于","val":"za"}

我的方法会创建一个这样的查询:

SELECT "individual_investors".* FROM "individual_investors" WHERE (first_name like %za%)

这个查询是错误的.我想咨询一下个人投资者对公司本轮融资的投资情况.这是很多连接表.

我使用的方法适用于单一模型，无法解决我上面提到的问题.

我该如何解决这个问题?

解决方案

您可以根据哈希创建 SQL 查询.最通用的方法是原始 SQL，它可以由 ActiveRecord 执行.

这里是一些概念代码，应该可以给你正确的想法:

query_select = "select * from "query_where = ""table = [] # 用于从所有表中选择hash.each 做 |表，值|table_name = table.constantize.table_name表<<表名values.each 做 |q|query_where += " AND " 除非 query_string.empty?query_where += "'#{ActiveRecord::Base.connection.quote(table_name)}'."query_where += "'#{ActiveRecord::Base.connection.quote(q[fieldName)}'"if q[:operator] == "starts with" # 这应该用适当的方法完成query_where += " LIKE '#{ActiveRecord::Base.connection.quote(q[val)}%'"结尾结尾结尾query_tables = tables.join(", ")raw_query = query_select + query_tables + " where " + query_where结果 = ActiveRecord::Base.connection.execute(raw_query)result.to_h # 不是必需的，但原始结果可能更容易作为哈希处理

这是做什么的:

• query_select 指定你想要的结果中的信息
• query_where 构建所有搜索条件并转义输入以防止 SQL 注入
• query_tables 是您需要搜索的所有表的列表
• table_name = table.constantize.table_name 将为您提供模型使用的 SQL table_name
• raw_query 是上述部分的实际组合 sql 查询
• ActiveRecord::Base.connection.execute(raw_query) 在数据库上执行sql

确保将任何用户提交的输入放在引号中并正确转义以防止 SQL 注入.

对于您的示例，创建的查询将如下所示:

select * from Companies, Categories where 'companies'.'name' LIKE 'a%' AND 'companys'.'hq_city' = 'karachi' AND 'categories'.'name' NOT LIKE '%ECommerce%'

这种方法可能需要额外的逻辑来连接相关的表.在您的情况下，如果 company 和 category 有关联，则必须将类似的内容添加到 query_where

"AND 'company'.'category_id' = 'categories'.'id'"

简单方法:您可以为所有可以查询的模型/表对创建一个哈希，并在那里存储适当的连接条件.即使对于中型项目，这个 Hash 也不应该太复杂.

硬方法:这可以自动完成，如果您正确定义了has_many、has_one 和belongs_to在你的模型中.您可以使用 reflect_on_all_associations.实施 Breath-First-Search 或 Depth-First Search 算法，并从任何模型开始，并从您的 json 输入中搜索与其他模型的匹配关联.开始新的 BFS/DFS 运行，直到 json 输入中没有未访问的模型为止.从找到的信息中，您可以导出所有连接条件，然后将它们作为表达式添加到原始 sql 方法的 where 子句中，如上所述.更复杂但也可行的是读取数据库 schema 并使用此处定义的类似方法，通过查找 外键.

使用关联:如果所有关联都与 has_many/has_one 关联，您可以使用 ActiveRecord 通过在最重要"模型上使用 joins 方法和 inject ，如下所示:

base_model = "公司".constantize关联 = [:categories] # 等等结果 = assocations.inject(base_model) { |模型，关联|model.joins(assoc) }.where(query_where)

这是做什么的:

• 它将 base_model 作为起始输入传递给 Enumerable.inject，它会重复调用 input.send(:joins, :assoc) (在我的例子中，这将执行 Company.send(:joins, :categories) 相当于 `公司.categories
• 在组合连接上，它执行 where 条件(如上所述构造)

免责声明您需要的确切语法可能因您使用的 SQL 实现而异.

Im trying to replicate the searching list style of crunchbase using ruby on rails. I have an array of filters that looks something like this:

[
   {
      "id":"0",
      "className":"Company",
      "field":"name",
      "operator":"starts with",
      "val":"a"
   },
   {
      "id":"1",
      "className":"Company",
      "field":"hq_city",
      "operator":"equals",
      "val":"Karachi"
   },
   {
      "id":"2",
      "className":"Category",
      "field":"name",
      "operator":"does not include",
      "val":"ECommerce"
   }
]

I send this json string to my ruby controller where I have implemented this logic:

filters = params[:q]
table_names = {}
filters.each do |filter|
    filter = filters[filter]
    className = filter["className"]
    fieldName = filter["field"]
    operator = filter["operator"]
    val = filter["val"]
    if table_names[className].blank? 
        table_names[className] = []
    end
    table_names[className].push({
        fieldName: fieldName,
        operator: operator,
        val: val
    })
end

table_names.each do |k, v|
    i = 0
    where_string = ''
    val_hash = {}
    v.each do |field|
        if i > 0
            where_string += ' AND '
        end
        where_string += "#{field[:fieldName]} = :#{field[:fieldName]}"
        val_hash[field[:fieldName].to_sym] = field[:val]
        i += 1
    end
    className = k.constantize
    puts className.where(where_string, val_hash)
end

What I do is, I loop over the json array and create a hash with keys as table names and values are the array with the name of the column, the operator and the value to apply that operator on. So I would have something like this after the table_names hash is created:

{
   'Company':[
      {
         fieldName:'name',
         operator:'starts with',
         val:'a'
      },
      {
         fieldName:'hq_city',
         operator:'equals',
         val:'karachi'
      }
   ],
   'Category':[
      {
         fieldName:'name',
         operator:'does not include',
         val:'ECommerce'
      }
   ]
}

Now I loop over the table_names hash and create a where query using the Model.where("column_name = :column_name", {column_name: 'abcd'}) syntax.

So I would be generating two queries:

SELECT "companies".* FROM "companies" WHERE (name = 'a' AND hq_city = 'b')
SELECT "categories".* FROM "categories" WHERE (name = 'c')

I have two problems now:

1. Operators:

I have many operators that can be applied on a column like 'starts with', 'ends with', 'equals', 'does not equals', 'includes', 'does not includes', 'greater than', 'less than'. I am guessing the best way would be to do a switch case on the operator and use the appropriate symbol while building the where string. So for example, if the operator is 'starts with', i'd do something like where_string += "#{field[:fieldName]} like %:#{field[:fieldName]}" and likewise for others.

So is this approach correct and is this type of wildcard syntax allowed in this kind of .where?

2. More than 1 table

As you saw, my approach builds 2 queries for more than 2 tables. I do not need 2 queries, I need the category name to be in the same query where the category belongs to the company.

Now what I want to do is I need to create a query like this:

Company.joins(:categories).where("name = :name and hq_city = :hq_city and categories.name = :categories[name]", {name: 'a', hq_city: 'Karachi', categories: {name: 'ECommerce'}})

But this is not it. The search can become very very complex. For example:

A Company has many FundingRound. FundingRound can have many Investment and Investment can have many IndividualInvestor. So I can select create a filter like:

{
  "id":"0",
  "className":"IndividualInvestor",
  "field":"first_name",
  "operator":"starts with",
  "val":"za"
} 

My approach would create a query like this:

SELECT "individual_investors".* FROM "individual_investors" WHERE (first_name like %za%)

This query is wrong. I want to query the individual investors of the investments of the funding round of the company. Which is a lot of joining tables.

The approach that I have used is applicable to a single model and cannot solve the problem that I stated above.

How would I solve this problem?

解决方案

You can create a SQL query based on your hash. The most generic approach is raw SQL, which can be executed by ActiveRecord.

Here is some concept code that should give you the right idea:

query_select = "select * from "
query_where = ""
tables = [] # for selecting from all tables
hash.each do |table, values|
  table_name = table.constantize.table_name
  tables << table_name
  values.each do |q|
    query_where += " AND " unless query_string.empty?
    query_where += "'#{ActiveRecord::Base.connection.quote(table_name)}'."
    query_where += "'#{ActiveRecord::Base.connection.quote(q[fieldName)}'"
    if q[:operator] == "starts with" # this should be done with an appropriate method
      query_where += " LIKE '#{ActiveRecord::Base.connection.quote(q[val)}%'"
    end
  end
end
query_tables = tables.join(", ")
raw_query = query_select + query_tables + " where " + query_where 
result = ActiveRecord::Base.connection.execute(raw_query)
result.to_h # not required, but raw results are probably easier to handle as a hash

What this does:

• query_select specifies what information you want in the result
• query_where builds all the search conditions and escapes input to prevent SQL injections
• query_tables is a list of all the tables you need to search
• table_name = table.constantize.table_name will give you the SQL table_name as used by the model
• raw_query is the actual combined sql query from the parts above
• ActiveRecord::Base.connection.execute(raw_query) executes the sql on the database

Make sure to put any user submitted input in quotes and escape it properly to prevent SQL injections.

For your example the created query will look like this:

select * from companies, categories where 'companies'.'name' LIKE 'a%' AND 'companies'.'hq_city' = 'karachi' AND 'categories'.'name' NOT LIKE '%ECommerce%'

This approach might need additional logic for joining tables that are related. In your case, if company and category have an association, you have to add something like this to the query_where

"AND 'company'.'category_id' = 'categories'.'id'"

Easy approach: You can create a Hash for all pairs of models/tables that can be queried and store the appropriate join condition there. This Hash shouldn't be too complex even for a medium-sized project.

Hard approach: This can be done automatically, if you have has_many, has_one and belongs_to properly defined in your models. You can get the associations of a model using reflect_on_all_associations. Implement a Breath-First-Search or Depth-First Search algorithm and start with any model and search for matching associations to other models from your json input. Start new BFS/DFS runs until there are no unvisited models from the json input left. From the found information, you can derive all join conditions and then add them as expressions in the where clause of the raw sql approach as explained above. Even more complex, but also doable would be reading the database schema and using a similar approach as defined here by looking for foreign keys.

Using associations: If all of them are associated with has_many / has_one, you can handle the joins with ActiveRecord by using the joins method with inject on the "most significant" model like this:

base_model = "Company".constantize
assocations = [:categories]  # and so on
result = assocations.inject(base_model) { |model, assoc| model.joins(assoc) }.where(query_where)

What this does:

• it passes the base_model as starting input to Enumerable.inject, which will repeatedly call input.send(:joins, :assoc) (for my example this would do Company.send(:joins, :categories) which is equivalent to `Company.categories
• on the combined join, it executes the where conditions (constructed as described above)

Disclaimer The exact syntax you need might vary based on the SQL implementation you use.

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