Mongoid 3 Complex query with AND and ORs

Question

I have a Rails 3.2 Blog app that displays posts for users based on their company and their region. I also want to show messages in which the user is the author of the message, even if it was created for another company or region (the Post object has a region and a company, as well as a User object).

I did something similar in MySQL with a query:

SELECT * FROM Post WHERE approved = true AND hold = false AND ((company_id IN (null, 'user_company_id') AND region IN (null, 'user_region_id')) OR author = 'user_id') ORDER BY published_date; 

Now we are porting the application to MongoDB using the Mongolian gem, and this is the last request that I cannot understand. Here is what I got so far, which uses a combination of Origin and Selector to make calls in a document:

 @posts = Post.and( {approved: true}, {hold_publish: false}, {"$or" => [ {"$and" => [{:company_id.in => [nil, @user.company.id]}, {:region_id.in => [nil, @user.region.id]}]},{user_id: @user.id} ]}).desc(:published_date) 

However, this will only result in deleting messages where company_id and region_id are null or correspond to user attributes.

Answer 1

Here is your request:

 Post.where(approved: true,hold_publish: true) .or(:company_id.in => [nil, @user.company.id],:region_id.in => [nil, @user.region.id]) .or(user_id: @user.id) .desc(:published_date)