Hibernate version: 3.2.4SP1

I have a need to write a sqlRestriction that uses a column on a join table. Even though I have created a alias for the table, Hibernate uses a different alias when the sql is constructed.
Below is a code snippet of what I am trying to do.



The above code doens't work as the alias for company is not c but something like c1_. How do I get the hibernate generated alias for the join table Passing in {c} instead of c doesn't work either.

Thanks

Rajiv

Hi Rajiv

Rather than tacking on SQL restrications directly, you should use the Restriction API to create your entire query using the alias names you have defined. Hibernate will then convert your aliases into the ones it uses in the actual query.

i.e.


Mike

Mike this doesn't solve my problem. I use the Restrictions API for single field criteria but for this particluar case I have a need to query on 2 fields at the same time.
For example assuming person.name = Bob and person company = Intel using your approach if one were to search on 'Intel bob' no results will be returned. I want a search on intel bob to return a result.

Agreed. I misunderstood your intentions.

After messing with this for a while I don't think there's any need to use aliases unless you're referencing columns of the same name from different tables - i.e. there's an ambiguity with which columns the 'where clause' is refering to.

Instead of an alias try using a sub-criteria on the joined class - like this:



The downside of using sqlRestrictions like this is the coupling with the database column names and lack of portability. For example, the above SQL doesn't work with my MYSQL database. I have to use the concat function instead of ||.

Perhaps a better solution would be to use HQL through the Query API. I'm assuming here that both companyName and name are properties of the Company object:



If name is from Person and companyName from Company then its simply a matter of doing this:



THe above is more portable because it doesn't reference column names directly. Hibernate changes the property style references to column names. Additionally, with the MYSQL dialect Hibernate changes the || operator to 'concat' in the resulting SQL. Amazing!

Mike