如何使用 Arel 为带有“or"和“and"子句的 SQL 查询正确添加括号? [英] How to properly add brackets to SQL queries with 'or' and 'and' clauses by using Arel?

问题描述

我使用的是 Ruby on Rails 3.2.2，我想生成以下 SQL 查询:

I am using Ruby on Rails 3.2.2 and I would like to generate the following SQL query:

SELECT `articles`.* FROM `articles` WHERE (`articles`.`user_id` = 1 OR `articles`.`status` = 'published' OR (`articles`.`status` = 'temp' AND `articles`.`user_id` IN (10, 11, 12, <...>))) 

通过使用 Arel 这种方式

Article
 .where(
   arel_table[:user_id].eq(1)
   .or(arel_table[:status].eq("published"))
   .or(
     arel_table[:status].eq("temp")
     .and(
       arel_table[:user_id].in(10, 11, 12, <...>)
     )
  )
)

它生成以下内容(注意:括号与第一个 SQL 查询不同):

it generates the following (note: brackets are not the same as the first SQL query):

SELECT `articles`.* FROM `articles` WHERE (((`articles`.`user_id` = 1 OR `articles`.`status` = 'published') OR `articles`.`status` = 'temp' AND `articles`.`user_id` IN (10, 11, 12, <...>))) 

因为我认为后一个 SQL 查询不能像第一个那样工作"，我怎么能使用 Arel(或者，也许是其他东西)所以要生成第一个 SQL 查询?

Since I think the latter SQL query doesn't "work" as the first one, how could I use Arel (or, maybe, something else) so to generate the SQL query as the first one?

鉴于上面的 SQL 查询工作"相同，但我仍然希望生成确切的 SQL 查询作为问题中的第一个查询(这样做的主要原因是第一个 SQL 查询比第二个更具可读性，因为在第一个中使用较少且显式"括号)，我如何使用 Arel 做到这一点?

Given SQL queries above "work" the same but I still would like to generate the exact SQL query as the first one in the question (the main reason to make this is that the first SQL query is more readable than the second since in the first one are used less and "explicit" brackets), how could I make that by using Arel?

推荐答案

我已经成功使用了这个 gem:squeel 位于 Arel 之上，因此您不必弄乱它.因此，为了生成您的查询，您可以在 Squeel 中执行以下操作:

I've successfully used this gem: squeel which comes on top of Arel so you don't have to mess with it. So in order to generate your query you would do something like this in Squeel:

@articles = Article.
  where{
  ( user_id.eq(1) | status.eq('published') ) |
  ( user_id.in([10, 11, 12, '<...>']) & status.eq('temp') )
}

# since this is an ActiveRecord::Relation we can play around with it
@articles = @articles.select{ [ user_id, status ] }

# and you can also inspect your SQL to see what is going to come out
puts @articles.to_sql

您的查询越复杂，您就越会喜欢这个 gem.

The more complicated your queries get the more you're going to like this gem.

这篇关于如何使用 Arel 为带有“or"和“and"子句的 SQL 查询正确添加括号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！