Hi,

I'm having trouble formulating a query with criteria. I'd like users to enter a search term, and have it check the term against User.first_name, User.last_name, and User.company.company_name. (company is a many-to-one property of User that returns a Company object).

This works without the company name:



but this gives an error.


Reading the docs, it looks like I need a "createCriteria" but I can't figure out where to put it.

Any suggestions? I'd like to use the Criteria object for querying since I have a filter with a number of properties that can all optionally be applied.

WILL

I tried that, it's not quite what I'm looking for. The expression you gave is the equivalent of an AND. It returns records where the company name matches AND either the first or last name match.

I'm looking for a 3 way OR. Either the first name OR the last name OR the company name could match. The idea is that the user enters a phrase e.g. "Smith" and it would match "John Smithson / IBM" or "Smith Jefferson / Nestle" or "John Doe / Smith and Sons".

WILL

Try this:

Works great! This might be a nice entry for the Advanced FAQ as I couldn't find a discussion of this anywhere. (I didn't really understand what createAlias meant)

For the record, the correct code is a little different. It is:

You could also do

definitely a little easier to understand, thanks.

Also, instead of the string concatenation, use: Expression.like("foo", foo, MatchMode.ANYWHERE)

Thanks for the tips -- very helpful.

One more question. I tried this, and the problem now is that any matched record is required to have a company object joined. But some of the User objects have a null for the company property. For example, there might be a User with a matching first_name but no company present. The example given will not match this record, since a valid company (matching or not) is required by the join

Is there any way to make the join an outer join instead of an inner join?

Thanks, WILL

P.S. As a side note (related to the recent blog entry), pfui to those who complain about the support for this project. It amazes me that such a popular project has great documentation and support. I've always had my questions answered (if sometimes a bit curtly). Look forward to purchasing the book.

I don't think this is possible with the Criteria API yet - most likely you will have to use two queries.