Hello, I have the following :

select from Company as c where (c.dbaName LIKE :name or c.taxpayer.name LIKE :name)

the c.taxpayer.name causes it to automatically create the correct table join and where condition. The problem is where it's putting the paranthesis. Here is the resulting SQL:

select company0_.MASTER_NO ... from MASTER company0_, PARTY party1_ where ((company0_.DBA_NAME LIKE ? )or(party1_.name LIKE ? and company0_.TAXPAYER=party1_.PARTY_NO))

where it should be:

select company0_.MASTER_NO ... from MASTER company0_, PARTY party1_ where (company0_.DBA_NAME LIKE ? or party1_.name LIKE ? ) and company0_.TAXPAYER=party1_.PARTY_NO

What am I doing wrong, how do I do this correctly? Thanks.

and
are equivalent.

Not if you run it on an oracle database. The first query returns thousands of records, the second returns 86.

I guess because if party1_.name LIKE ? is false, it doesn't evaluate company0_.TAXPAYER=party1_.PARTY_NO, thus it returns all companys with DBA_NAME LIKE ? joined with each record from the party table.

Sorry - on second thought they actually aren't equivalent queries. You have a valid point.

No problem. Any idea on how to do this? It seems simple enough...

Equivalent "cat" example would be:

select from Cat as c where (c.name LIKE :name or c.owner.name LIKE :name)

No problem. Any idea on how to do this? It seems simple enough...

Equivalent "cat" example would be:

select from Cat as c where (c.name LIKE :name or c.owner.name LIKE :name)

Anybody? Something this simple has to work in Hibernate, I must be doing something wrong. Any help would be greatly appreciated. Thanks.

You've obviously got some inheritance heirarchy mapped here. I suspect that Hibernate IS generating the correct query but cannot confirm unless I see the mappings, the query code and complete generated SQL

Here are the mappings:

<hibernate-mapping>

<class name="model.Company" table="MASTER">

<id name="masterNo" type="int" unsaved-value="null" >
<column name="MASTER_NO" not-null="true"/>
<generator class="sequence">
<param name="sequence">master_seq</param>
</generator>
</id>
<property name="taxpayerNo">
<column name="TAXPAYER_NO" />
</property>
<many-to-one name="taxpayer" class="model.Party" column="TAXPAYER"/>
<property name="dbaName">
<column name="DBA_NAME" />
</property>
<property name="bizDesc">
<column name="BIZ_DESC" />
</property>
<property name="bizCode">
<column name="BIZ_CODE" />
</property>
<property name="charterNo">
<column name="CHARTER_NO" />
</property>
<property name="fedId">
<column name="FED_ID" />
</property>
<many-to-one name="prez" class="model.Party" column="PREZ"/>
<many-to-one name="vp" class="model.Party" column="VP"/>
<many-to-one name="secTrea" class="model.Party" column="SEC_TREA"/>
<property name="ownType">
<column name="OWN_TYPE" />
</property>
<property name="comments">
<column name="COMMENTS" />
</property>
<property name="inactiveDate">
<column name="INACTIVE_DATE" />
</property>
<many-to-one name="mailAddr" class="model.Address" column="MAIL_ADDR"/>
<property name="operInit">
<column name="OPER_INIT" />
</property>
<property name="maintDate">
<column name="MAINT_DATE" />
</property>

<set name="vendors" inverse="true" lazy="true">
<key column="MASTER_NO"/>
<one-to-many class="model.Vendor"/>
</set>

<set name="cigarettes" inverse="true" lazy="true">
<key column="MASTER_NO"/>
<one-to-many class="model.Cigarette"/>
</set>

<property name="phone"/>
<property name="fax"/>

</class>

</hibernate-mapping>

<hibernate-mapping>

<class name="model.Party" table="PARTY">

<id name="partyNo" type="int" unsaved-value="null" >
<column name="PARTY_NO" not-null="true"/>
<generator class="sequence">
<param name="sequence">party_seq</param>
</generator>
</id>

<property name="name" />
<property name="ssn" />
<many-to-one name="addr" class="model.Address" column="ADDR"/>

</class>

</hibernate-mapping>

Here is the query:

Query q = s.createQuery("select from Company as c where " +
"(c.taxpayer.name LIKE :name or c.dbaName LIKE :name)"
);

Here is the generated SQL:

Hibernate: select company0_.MASTER_NO as MASTER_NO, company0_.TAXPAYER_NO as TAXPAYER2_, company0_.TAXPAYER as TAXPAYER, company0_.DBA_NAME as DBA_NAME, company0_.BIZ_DESC as BIZ_DESC, company0_.BIZ_CODE as BIZ_CODE, company0_.CHARTER_NO as CHARTER_NO, company0_.FED_ID as FED_ID, company0_.PREZ as PREZ, company0_.VP as VP, company0_.SEC_TREA as SEC_TREA, company0_.OWN_TYPE as OWN_TYPE, company0_.COMMENTS as COMMENTS, company0_.INACTIVE_DATE as INACTIV14_, company0_.MAIL_ADDR as MAIL_ADDR, company0_.OPER_INIT as OPER_INIT, company0_.MAINT_DATE as MAINT_DATE, company0_.phone as phone, company0_.fax as fax from MASTER company0_, PARTY party1_ where ((party1_.name LIKE ? and company0_.TAXPAYER=party1_.PARTY_NO)or(company0_.DBA_NAME LIKE ? ))