Many selects with simple one-to-one [1-to-1]

wiler · **Joined:** Sat Apr 10, 2004 1:19 pm **Posts:** 2

Hi,

I have a simple one-to-one assiocation between two entities. The association is modeled with a foreign key in on entity
(as shown in the manual):

+===+ ------------------- +====+
| User |-(0..1)--------(1)-| Folder |
+===+ ------------------- +====+

So every time a user is created, a private folder is created for the user. But there can be (public) folders that have
no private users (0..1).
The folder has a "createdByUser" as foreign key marker.

Code:

<!-- USER -->
<class name="User" table="USER">
   <id name="id" type="string" unsaved-value="null" >
      <column name="ID" sql-type="char(32)" not-null="true"/>
      <generator class="uuid.hex"/>
   </id>
   ... (props)
   <one-to-one name="privateFolder"
      class="Folder"
      property-ref="createdByUser"
      outer-join="true"/>
</class>

<!-- FOLDER -->
<class name="Folder" table="FOLDER">
   <id name="id" type="string" unsaved-value="null" >
      <column name="ID" sql-type="char(32)" not-null="true"/>
      <generator class="uuid.hex"/>
      </id>
    ... (props)
   <!-- in fact it is a one-to-one-->
   <many-to-one
      name="createdByUser" 
      class="User" 
      column="CREATEDBYUSER_ID"
      not-null="true"
      outer-join="true"/> 
</class>

Now, when I load a User like

Code:

session.load(User.class, givenid)

the sql trace shows two selects (the syntax is HQL-style symbolic):

Code:

select user, folder from User user left outer join Folder folder on user.id = folder.folder.createdbyuser_id where user.id = givenid

Code:

select user, folder from User user left outer join Folder folder on user.id = folder.folder.createdbyuser_id where folder.createdbyuserid = givenid

Why is there a 2nd join-select fired producing the same result as the first one?

----

When I try to load the User like this:

Code:

Query q = session.createQuery(
"select user, folder from User as user, Folder as folder " +
"where user = folder.createdByUser " +
"and user.id = ?");
q.setString(0, givenuserId);
List aList = q.list();

if(aList != null) {
   Iterator iter = aList.iterator();
   while (iter.hasNext()) {
      Object[] pair = (Object[]) iter.next();
      aUser = (User) pair[0];
      aFolder = (Folder) pair[1];
      log.debug("Found user: " + aUser.getId());
      log.debug("Found folder: " + aFolder.getId());
   }
}            

aUser.setPrivateFolder(aFolder);
aFolder.setCreatedByUser(aUser);
...

Again, two selects are executed:

Code:

select user, folder from User user, Folder folder where user.id =  folder.createdbyuser_id and user.id = givenuserId

Code:

select user, folder from User user left outer join Folder folder on user.id = folder.folder.createdbyuser_id where folder.createdbyuserid = givenuserid

Basically, the second select is redundant, isn

emmanuel · **Posted:** Fri Apr 30, 2004 1:34 pm

Map both side with a one-to-one