performing nested select using grails and gorm

Question

I have the following SQL query, I was wondering if I could rewrite this in GORM style with grails, possibly using criteria, where, or other programming style querying that HQL.

SELECT count(USERID) as Result FROM Answer
WHERE USERID IN
      (SELECT USERID FROM Answer
       WHERE USERID IN (SELECT USERID FROM Answer
             WHERE USERID IN (SELECT USERID FROM Answer WHERE QID=1 AND ATxt='30') AND 
       QID=2 AND ATxt='M') AND QID=3 AND ATxt='6') AND QID=4 AND ATxt='160'

Answer 1

Instead of using this nested in's you could use or. Without knowing your domain classes, I'm assuming that you have something like:

class User {
}

class Question {
}

class Answer {
  static belongsTo = [user: User, question: Question]
}


def question2 = Question.findById(2)
def question3 = Question.findById(3)
def question4 = Question.findById(4)

Answer.withCriteria {
  projections {
    count('user')
  }
  or {
    and {
      eq('question', question2)
      eq('aTxt', 'M')
    }
    and {
      eq('question', question3)
      eq('aTxt', '6')
    }
    and {
      eq('question', question4)
      eq('aTxt', '160')
    }
  }
}