I really need some help working out this query... I just can't make it fly...
my tables are (simplified)
Author - maps to Author object
--------
id: key
name: string
Resource - maps to Resource object
-----------
id: key
name: string
authorContributions: set of (author, contribution)
Contribution - maps to Contribution object
--------------
id: key
name: string
Resource_Author_Contribution - does not map to an object, just represents the relationship between the tables.
-----------------------------------
resource_id: key
author_id: key
contribution_id: key
I want my query to select all authors, once, that have an entry in Resource_Author_Contribution.author_id field. i.e. only select authors that have an entry against a resource.
The SQL looks like this
select * from Author as a
where a.id in
(
select a2.id from author as a2, resource_author_contribution as rac2, resource r2
where r2.id = rac2.resource_id and
a2.id = rac2.author_id
group by a2.id
)
pretty straight forward really. I just can't make it work with hibernate (I've been using hibern8ide for many, many hours now) - I've tried all combinations of elements() and joins, but my brain just isn't getting it.
Mark
Here is my mapping files (cut down a bit)
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 1.1//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class
name="com.stateofflux.cms.datatype.StandardResource"
table="RESOURCE"
>
<id
name="id"
column="ID"
type="java.lang.Integer"
unsaved-value="-99"
>
<generator class="native">
</generator>
</id>
<many-to-one
name="owner"
class="com.stateofflux.cms.datatype.User"
cascade="none"
outer-join="auto"
column="OWNER_ID"
not-null="true"
/>
<set
name="authorContributions"
table="RESOURCE_AUTHOR_CONTRIBUTION"
lazy="true"
inverse="false"
cascade="all"
sort="unsorted"
>
<key
column="RESOURCE_ID"
/>
<composite-element
class="com.stateofflux.cms.datatype.AuthorContribution"
>
<many-to-one
name="author"
class="com.stateofflux.cms.datatype.Author"
cascade="none"
outer-join="auto"
column="AUTHOR_ID"
/>
<many-to-one
name="contribution"
class="com.stateofflux.cms.datatype.Contribution"
cascade="none"
outer-join="auto"
column="CONTRIBUTION_ID"
/>
</composite-element>
</set>
<property
name="name"
type="java.lang.String"
column="NAME"
not-null="true"
/>
<joined-subclass
name="com.stateofflux.cms.datatype.BlobResource"
table="BLOB_RESOURCE"
>
<key
column="BLOB_ID"
/>
<joined-subclass
name="com.stateofflux.cms.datatype.ImageResource"
table="IMAGE_RESOURCE"
>
<key
column="IMAGE_ID"
/>
<property
name="width"
type="int"
column="WIDTH"
not-null="true"
/>
<property
name="height"
type="int"
column="HEIGHT"
not-null="true"
/>
<property
name="filename"
type="java.lang.String"
column="FILENAME"
not-null="true"
/>
<joined-subclass
name="com.stateofflux.cms.datatype.StreetArt"
table="STREET_ART"
>
<key
column="STREET_ART_ID"
/>
<many-to-one
name="location"
class="com.stateofflux.cms.datatype.PhysicalLocation"
cascade="none"
outer-join="auto"
column="PHYSICAL_LOCATION_ID"
/>
<many-to-one
name="thumbnail"
class="com.stateofflux.cms.datatype.StandardResource"
cascade="none"
outer-join="auto"
column="THUMBNAIL_ID"
/>
<property
name="streetArtType"
type="com.stateofflux.cms.hibernate.StreetArtTypeEnum"
column="STREET_ART_TYPE"
length="15"
not-null="true"
/>
</joined-subclass>
</joined-subclass>
</joined-subclass>
</class>
</hibernate-mapping>
The Author and Contribution mappings are very boring so I've left them out.
Login
FAQ
Search