Hibernate Criteria query for matching on fields of related objects?

[Steven Gollery]

In my model, I have a PhoneNumber class that breaks the number into 3 fields:

class PhoneNumber {
private Long id;
private String areaCode;
private String prefix;
private String number;
}

Then I have a Person class that has references to 3 phone numbers:

class Person {
private Long id;
private PhoneNumber cell;
private PhoneNumber home;
private PhoneNumber work;
}


These are mapped to two tables in the database, with cell, home and work columns in the Person table that are foreign keys into the PhoneNumber
table.

Now I need to allow users to find Person objects by entering the last 4 digits of the phone number. The query should match against the 'number' field of any of the Person's three phone numbers.

I'm having trouble coming up with a query that works. My first try was this:

Criteria query = session.createCriteria(Person.class);
LogicalExpression cellOrHome = Expression.or(Expression.like("cell.number", "%" + searchString),                                                              
                                             Expression.like("home.number", "%" + searchString));
query.add(Expression.or(cellOrHome, Expression.like("work.number", "%" + searchString)));

This was supposed to set up an OR with each number, but of course it doesn't work because there are no fields named "cell.number", "home.number", or "work.number" in Person.

I then tried nested Criteria objects, something like this:

[CODE}
Criteria home = query.createCriteria("home");
home.add(Expression.like("number", "%" + searchString));
Criteria work = query.createCriteria("work");
work.add(Expression.like("number", "%" + searchString));
Criteria cell= query.createCriteria("cell");
cell.add(Expression.like("number", "%" + searchString));
[/CODE]

Now the query only returns Persons where all three numbers match the search strings. I can't see how to turn this approach into an OR.

Does anyone have any ideas how to approach this problem?

(It's too late in the project to change either the model or the database tables.)

Thanks in advance,


Steven Gollery

[Steven Gollery]

I got it: two ways to do it. Either use a filter or combine the nested criteria using Expression.or. The filter is straightforward:

<filter name="phoneSearch" condition=":number = (select p.number from PhoneNumber p where p.phone_key = work) OR :number = (select p.number from PhoneNumber p where p.phone_key = cell) OR :number = (select p.number from PhoneNumber p where p.phone_key = home) "/> 

Steven