Force Inner Join on Collections? Better mapping ideas?

Woil · **Posted:** Wed Jan 23, 2008 3:09 pm

Hibernate version: 1.2.1.4000 (edge)

Mapping documents:

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
    namespace="Zeus.Models" assembly="Zeus">

  <class name="Toy" table="Toys">
    <id name="Id" type="Int32" column="ID" unsaved-value="0">
      <generator class="native" />
    </id>
    <bag name="TemporalData" inverse="true" lazy="false" cascade="all" fetch="join" check="">
      <key column="ToyID" />
      <one-to-many class="Toy_Data" not-found="ignore"/>
      <filter name="ValidDate" condition=":validDate BETWEEN Valid_Start and Valid_Stop" />
    </bag>

    <bag name="ToyOwnership" inverse="true" lazy="true">
      <key column="ToyID"/>
      <one-to-many class="ToyOwnership"/>
      <filter name="ValidDate" condition=":validDate BETWEEN Valid_Start and Valid_Stop" />
    </bag>
    
  </class>

  <class name="Toy_Data" table="Toys_Data">
    <id name="Id" type="Int32" column="ID" unsaved-value="0">
      <generator class="native" />
    </id>
    <many-to-one name="Toy" column="ToyID" />
    <property name="Name"/>
    <property name="Valid_Start"/>
    <property name="Valid_Stop"/>
    <filter name="ValidDate" condition=":validDate BETWEEN Valid_Start and Valid_Stop" />
  </class>

</hibernate-mapping>

Name and version of the database you are using: SQL Server 2005

-- -- --

Here's the deal:

This mapping is for a demo project I'm using to determine if I can accomplish temporal design using NHibernate. As such the table structure is simple:

Toys
ID

Toys_Data
ID
ToysID
Name
Valid_Start
Valid_Stop

Logically Toys represents a single entity with only one item from Toys_Data being current for a valid time of now. (Refer to
14.1 NHibernate filters for how to accomplish this feat, and Wikipedia for more on temporal database design.)

The need for two tables arises from other entities having a relationship with Toys such that there needs to be one ID that doesn't change for the foreign key. The c# classes implementing this relationship have properties on the Toy class that access TemporalData[0] so use is transparent those outside the class for most things.

All works well until a Toy needs to be deleted. We can't delete the entry out of the Toys or Toys_Data tables as we need to be able to review the history, so a logical delete is needed. This is accomplished by setting the Valid_Stop to be the current time. The issue is that when iterating over all the Toys in the database I now get a blank toy:

Toys
[ID] [Name]
1 Block
2 Bat
3 [...] <-- deleted a second ago

Therefore an INNER JOIN on the bag of temporal data is needed so that only Toys with 1 or more Toy_Data entries are returned from any query. (It is meaningless to return Toys that have been logically deleted.)

I've reviewed some of the NHibernate code and determined that adding a fetch="inner-join" mapping option would be quite a task.

Possible solutions:

1) I can do a filter or check constraint on a subquery such as (select count(*) from Toys_Data where [validdate] and [foreign key]) > 0

2) I can include a "is deleted" bit in the Toys table and put that in the check constraint

3) I can sacrifice the appropriate goats and hope that someone will add inner-join style mapping options.

The first two options cause a lot of extra SQL work that is really not needed as an inner join is the proper SQL way of accomplishing what is needed here.

Your feedback is appreciated.

Woil · **Posted:** Wed Jan 23, 2008 7:10 pm

I have successfully introduced a filter at the class level as follows:

Code:

<filter name="ValidDate" condition="(Select count(*) from Toys_Data td WHERE td.ToyID = ID AND :validDate BETWEEN td.Valid_Start and td.Valid_Stop) > 0" />

Unfortunatly this means that I'm doing both a sub select and an outer join to grab both of these pieces of information together. Running this in sql server profiler:

Code:

SELECT     Toys.ID, Toys_Data.Name
FROM         Toys LEFT OUTER JOIN Toys_Data ON
Toys_Data.ToyID = Toys.ID
WHERE     ((SELECT     COUNT(*) AS Expr1
                         FROM         Toys_Data AS td
                         WHERE     (ToyID = Toys.ID) AND ('1/22/2008 2:43:24 PM' BETWEEN Valid_Start AND Valid_Stop)) > 0)

GO

SELECT    Toys.ID, Toys_Data.Name
FROM         Toys INNER JOIN Toys_Data 
ON (Toys_Data.ToyID = Toys.ID) AND ('1/22/2008 2:43:24 PM' BETWEEN Valid_Start AND Valid_Stop)

GO

Shows me that the top subquery (the one used by NHibernate with a filter in place) is 58% of the batch while the bottom (the one I'd like to use) is 42% of the batch. Not a huge difference, but if we're dealing with a server under heavy load with thousands of rows the extra 10% might be a big deal.

Anyone know of a change to mapping structure that I could do here?