How to properly add brackets to SQL queries with 'or' and 'and' clauses by using Arel?
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.
Example of how to use arel_table.grouping(...)
as part of scope
# app/model/candy.rbclass 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
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 outputs @articles.to_sql
The more complicated your queries get the more you're going to like this gem.