Size of assosiation in Hibernate criteria.

Question

Hello guys.

I've faced with a problem when querying with Hibernate Criteria in Grails.

Take a look:

def visitors = Client.withCriteria{
          visits{
             use ( TimeCategory ) {between('date',date,date+1.month-1)}
          }
          sizeGe("visits",params.from)
          sizeLe("visits",params.to)
          fetchMode("visits", FM.JOIN)
};

I need only those clients, which has number of visits in month between from and to bounds.

But now size* restrictions is being applied to all visits. So if client has one visit in this month, and visit in previous month. And if I set from=2, this client will be in result. But it should not be there.

//UPD: the thing as that sizeGe and sizeLe restrictions work not as I expect.

From my point of view they should be applied after between restriction, but they not.

For example:

def client = new Client();

client.visits.add(new Visit(date:'2010-03-16'));
client.visits.add(new Visit(date:'2010-05-16'));
client.visits.add(new Visit(date:'2010-05-17'));
client.save();

And then I want if:

• date = Date.parse('yyyy-MM','2010-05')
• params.from=2

criteria should return this client, and if

• params.from = 3

not return;
But it returns, because sizeGe is being applied to all visits, no matter which date it has.

// END UPD.

Let me know if smth still isn't clear.

Any help is appreciated.

Thanks, Vova.

Answer 1

You'd need a having clause to do this and I found on issue open in Hibernate's Jira to add support for this.

Meanwhile you should use HQL and write the query by hand, see this. If you don't need to change the query's restrictions based on some selected fields you should I always go with HQL, it can be cached.

Answer 2

Thanks Felipe.

Also, I've choose to use sqlRestriction. Of course, it has some portability issues but it works:

def visitors = c.listDistinct{
        use(TimeCategory){
                visits{

                    between('date',date,date+1.month-1)
                }

                sqlRestriction(
                        """(
                            select count(*)
                                  from visit v
                            where
                                v.visitor_id={alias}.id
                                and v.date between 
                                      '${date.format("yyyy-MM-dd")}' 
                                      and '${(date+1.month-1).format("yyyy-MM-dd")}'
                            )
                            between ${params.from} and ${params.to}"""
                )
            }
    }

Thanks, Vova.