How to create a criteria in Grails with a conjunction(and) of two disjunctions(or) using MongoDB?

Question

I'm trying to get all the activities from a user or its teams also filtered by some types if some properties are set.

This is what I have right now:

Activity.withCriteria{
    and{
        or {
            eq 'user',myUser
            "in" 'team',userTeams
        }
        and{
            if (showA || showB || showC){
                or{
                    if (showA){
                        "in" "a", myAList
                    }
                    if (showB){
                        "in" "b", myBList
                    }
                    if (showC){
                        "in" "c",myCList
                    }
                }
            }
        }
    }
    order "date","desc"
    maxResults maxElements
}

Executing that, what I get it's the OR of user and team block and the showA, showB, showC block instead of the AND of those two blocks.

I'm using grails 2.2.1 (also using MongoDB GORM 1.2.0 without Hibernate)

EDIT:

I have been able to see the query that's sent to MongoDB and it's not doing the first part of the criteria. This is the query that's being passed to MongoDB:

query: { query: { $or: [ { a: { $in: [ "5191e2c7c6c36183687df8b6", "5191e2c7c6c36183687df8b7", "5191e2c7c6c36183687df8b8" ] } }, { b: { $in: [ "5191e2c7c6c36183687df8b9", "5191e2c7c6c36183687df8ba", "5191e2c7c6c36183687df8bb" ] } }, { c: { $in: [ "5191e2c7c6c36183687df8b5" ] } } ] }, orderby: { date: -1 } }  ntoreturn: 10 ntoskip: 0

EDIT: I have just seen that a JIRA has already been raised and it seems that's a MongoDB plugin problem... http://jira.grails.org/browse/GPMONGODB-296

It looks to me that you are missing a `}`. One that will close the first `and` or the first `or`. — Todd Murray, May 13 '13 at 15:49
That'true, I just copied it and I must have missed that, thanks. But that's not the main problem — Eylen, May 14 '13 at 06:27

score 1 · Answer 1 · 2013-05-14T11:38:34.490

1

You can think in your criteria in a SQL perspective.

and ((user = 'myUserValue'
 or   team in (...))
and(a in (...)
  or b in (...)
  or c in (...)))

So your or is applied to user and team, but I think you want something like:

or {
  and {
    eq 'user',myUser
    "in" 'team',userTeams
  }
  and{
    if (showA || showB || showC){
      or{
        if (showA){
          "in" "a", myAList
        }
        if (showB){
          "in" "b", myBList
        }
        if (showC){
          "in" "c",myCList
        }
      }
    }
  }
}

So the key here is that the block you declare is applied to what you have inside.

EDIT:

A good tip to inspect a criteria is to enable the output of sql's generated by Hibernate. This can be done in DataSource.groovy

dataSource {
  logSql = true
}

hibernate {
  format_sql = true
}

edited May 14 '13 at 11:38

answered May 13 '13 at 16:23

What I want to achieve is what you wrote in SQL format, but not what you wrote in code I think. I thought that my code format it's ok (or I can't see what's wrong) but it's not doing what I expected... – Eylen May 14 '13 at 06:26
Ok, so I edited my answer showing how you can debug your criteria. – May 14 '13 at 11:38
Thanks for the tip, I already knew that trick, but for MongoDB it does not work.... :( Also, I have edited my question as I have found this is a reported bug of the MongoDB plugin for Grails. Thanks for all :) – Eylen May 14 '13 at 14:32

score 0 · Accepted Answer · answered Oct 15 '13 at 06:20

0

With the new version of Mongo for Grails this has been fixed, so now it's working with version 1.3.0.

http://grails.org/plugin/mongodb

answered Oct 15 '13 at 06:20

Eylen

2,617
4
27
42

How to create a criteria in Grails with a conjunction(and) of two disjunctions(or) using MongoDB?

2 Answers2