How to properly add brackets to SQL queries with 'or' and 'and' clauses by using Arel?

Question

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, <...>))) 

By using Arel this way

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, <...>)
     )
  )
)

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, <...>))) 

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?

Update (after comments)

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?

Answer 1

I had the same problem. I was searching the web for some hours and finally found a method named grouping in Arel::FactoryMethods which simply adds brackets around an expression.

You should wrap your groups with a arel_table.grouping(...) call.

Answer 2

Example of how to use arel_table.grouping(...) as part of scope

# app/model/candy.rb
class Candy < ActiveRecord::Base
  has_many :candy_ownerships
  has_many :clients, through: :candy_ownerships, source: :owner, source_type: 'Client'
  has_many :users,   through: :candy_ownerships, source: :owner, source_type: 'User'

  # ....
  scope :for_user_or_global, ->(user) do
    # ->() is new lambda syntax, lamdba{|user| ....}

    worldwide_candies  = where(type: 'WorldwideCandies').where_values.reduce(:and)
    client_candies     = where(type: 'ClientCandies', candy_ownerships: { owner_id: user.client.id, owner_type: 'Client'}).where_values.reduce(:and)
    user_candies       = where(type: 'UserCandies',   candy_ownerships: { owner_id: user.id,        owner_type: 'User'  }).where_values.reduce(:and)

    joins(:candy_ownerships).where( worldwide_candies.or( arel_table.grouping(client_candies) ).or( arel_table.grouping(user_candies) ) )
  end

  # ....
end

call

Candy.for_user_or_global(User.last)
#=> SELECT `candies`.* FROM `candies` INNER JOIN `candy_ownerships` ON `candy_ownerships`.`candy_id` = `candies`.`id` WHERE (`candies`.`deleted_at` IS NULL) AND (((`candies`.`type` = 'WorldwideCandies' OR (`candies`.`type` = 'ClientCandies' AND `candy_ownerships`.`owner_id` = 19 AND `candy_ownerships`.`owner_type` = 'Client')) OR (`candies`.`type` = 'UserCandies' AND `candy_ownerships`.`owner_id` = 121 AND `candy_ownerships`.`owner_type` = 'User')))

thx micha for the tip