Mongoid 3 Complex Query with ANDs and ORs

Question

I have a Rails 3.2 Blog app which shows posts to users based on their company and their region. I also want to to show posts where the user is the post's author, even if it was created for another company or region (the Post object has a region and a company, as well as the User object).

I did something similar in MySQL with the 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;

We are now migrating the app to MongoDB using the Mongoid gem and this is the last query that I can not figure out. Here is what I got so far which uses a combination of Origin and Selector to make the calls to the 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 pull posts where the company_id and the region_id are null or match the users attributes.

Answer 1

Here is your query try this -

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)