I having problem with HQL queries that require JOINS in my application. If I try and access a property of the class I have no problem

ie.
select ip from IntellectualProperty as ip WHERE ip.status.id = 2 ORDER BY ip.title


but when I try to join classes together I get not results what so ever.

ie.
select ip from IntellectualProperty as ip join ip.usages as usage join ip.licences as licence join licence.licensors as licensor WHERE usage.project.id = 1 OR licensor.id = 294 ORDER BY ip.title

Can anyone see what I am not doing correctly.

Kind Regards,
Barry

public class IntellectualProperty extends BaseObject {

// Attributes =============================================================

private Long id;
private String title;
private String description;
private String acknowledgementNotes;
private String notes;
private String externalSystemID;

// Associations ===========================================================

public Set contentTypes = new HashSet(); // of type ContentType
public Long copyrightStatus;
public Set catalogs = new HashSet(); // of type Catalog
public Set storages = new HashSet(); // of type Storage
public Set contributors = new HashSet(); // of type Contributor
public Set licences = new HashSet(); // of type Licence
public Set usages = new HashSet(); // of type Use

public Set parents = new HashSet(); // of type IntellectualProperty parent element
public Set children = new HashSet(); // of type IntellectualProperty child elements

public Status status;
}

Hibernate version: 3

Mapping documents:
<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping

>

<class
name="au.edu.tlf.crisp2.model.IntellectualProperty"
table="intellectual_property"
>

<id
name="id"
column="id"
unsaved-value="null"
>

<!-- The generator-class attribute of @hibernate.id is deprecated, use the @hibernate.generator tag instead -->
<generator class="native">

</generator>

</id>

<property
name="title"
type="text"
column="title"
>

</property>

<property
name="description"
type="text"
column="description"
>

</property>

<property
name="acknowledgementNotes"
type="text"
column="ack_notes"
>

</property>

<property
name="notes"
type="text"
column="notes"
>

</property>

<set
name="contentTypes"
lazy="true"
cascade="all"
>

<!-- @hibernate.collection-key tag is deprecated, use @hibernate.key instead -->
<key
column="ip_content_type_id"
>

</key>

<!-- @hibernate.collection-one-to-many tag is deprecated, use @hibernate.one-to-many instead -->
<one-to-many
class="au.edu.tlf.crisp2.model.ContentType"
/>

</set>

<property
name="copyrightStatus"
column="copyright_status"
>

</property>

<many-to-one
name="status"
column="fk_status_intellectual_property_id"
>

</many-to-one>

<set
name="catalogs"
lazy="true"
cascade="all"
>

<!-- @hibernate.collection-key tag is deprecated, use @hibernate.key instead -->
<key
column="ip_catalog_id"
>

</key>

<!-- @hibernate.collection-one-to-many tag is deprecated, use @hibernate.one-to-many instead -->
<one-to-many
class="au.edu.tlf.crisp2.model.Catalog"
/>

</set>

<set
name="storages"
lazy="true"
cascade="all"
>

<!-- @hibernate.collection-key tag is deprecated, use @hibernate.key instead -->
<key
column="ip_storage_id"
>

</key>

<!-- @hibernate.collection-one-to-many tag is deprecated, use @hibernate.one-to-many instead -->
<one-to-many
class="au.edu.tlf.crisp2.model.Storage"
/>

</set>

<set
name="contributors"
lazy="true"
cascade="all"
>

<!-- @hibernate.collection-key tag is deprecated, use @hibernate.key instead -->
<key
column="ip_contributor_id"
>

</key>

<!-- @hibernate.collection-one-to-many tag is deprecated, use @hibernate.one-to-many instead -->
<one-to-many
class="au.edu.tlf.crisp2.model.Contributor"
/>

</set>

<set
name="licences"
table="table_ip_licence"
lazy="true"
cascade="save-update "
>

<!-- @hibernate.collection-key tag is deprecated, use @hibernate.key instead -->
<key
column="fk_ip_id"
>

</key>

<!-- @hibernate.collection-many-to-many tag is deprecated, use @hibernate.many-to-many instead -->
<many-to-many
class="au.edu.tlf.crisp2.model.Licence"
column="fk_licence_id"
outer-join="auto"
/>

</set>

<set
name="usages"
lazy="true"
cascade="all"
>

<!-- @hibernate.collection-key tag is deprecated, use @hibernate.key instead -->
<key
column="ip_usage_id"
>

</key>

<!-- @hibernate.collection-one-to-many tag is deprecated, use @hibernate.one-to-many instead -->
<one-to-many
class="au.edu.tlf.crisp2.model.IntellectualPropertyUsage"
/>

</set>

<set
name="parents"
lazy="true"
cascade="save-update"
>

<!-- @hibernate.collection-key tag is deprecated, use @hibernate.key instead -->
<key
column="parent_id"
>

</key>

<!-- @hibernate.collection-many-to-many tag is deprecated, use @hibernate.many-to-many instead -->
<many-to-many
class="au.edu.tlf.crisp2.model.IntellectualProperty"
column="child_id"
outer-join="auto"
/>

</set>

<set
name="children"
table="table_intellectual_property_composite"
lazy="true"
cascade="save-update"
>

<!-- @hibernate.collection-key tag is deprecated, use @hibernate.key instead -->
<key
column="child_id"
>

</key>

<!-- @hibernate.collection-many-to-many tag is deprecated, use @hibernate.many-to-many instead -->
<many-to-many
class="au.edu.tlf.crisp2.model.IntellectualProperty"
column="parent_id"
outer-join="auto"
/>

</set>

<property
name="externalSystemID"
column="external_system_id"
>

</property>

</class>

</hibernate-mapping>



Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using: MySQL 4.1.10

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

use p6spy or set show query to true in the hibernate config, see the query that is being sent to the DB, run it against the DB, and see why it is wrong or why there is no data

I did as suggested. I looked at the generated SQL. The problem became stranger, the full query code is as follows:



which returns nothing. But when I run the query to search either catalog table OR ip_usage table or their own I get a result for each. ie This query returns a results



And so it does when subsitute catalog for ip_usage but together they are failing?

I am constructing the query correctly? Can you do a OR over two separate table values?