Using subselect tag or creating a view

[Fletcher Munson]

I have a query (below) that needs to pull the most recent activity by transactionId (FK in a transactions table).

select tl.*
from dbo.TransactionsLog tl 
inner join dbo.PartnershipLegalEntityShareClassMap map on tl.mappingId=map.MappingId 
inner join dbo.LegalEntityShareClasses lesc on map.legalEntityShareClassID=lesc.LegalEntityShareClassID 
inner join dbo.LegalEntities le on lesc.LegalEntityID=le.LegalEntityId 
inner join (
select max(modificationDate) as modificationDate, transactionId as transactionId 
from dbo.TransactionsLog 
group by transactionId) v on v.modificationDate = tl.modificationDate and v.transactionId = tl.transactionId
where <some critiera>

The query works exactly how I want it, but when I try to use it in Hibernate I have trouble converting it over. I currently use the SQLQuery interface to run this, but I would like to use a criteria query though I can't seem to find any way to do it. The closest I've come was the <subselect> tag in the mapping file to attempt to create a view. I'm not seeing much documentation on this tag, and it's not totally clear how to use it or what I'm doing wrong. In fact, I'm not ever sure how to access/utilize the view via java after it's mapped properly (DetachedCritiera maybe?).

<hibernate-mapping schema="dbo">

	<!-- this is what I'm doing to try to create "immutable mapping" for my view -->
	<class
		name="com.mycompany.transaction.TransactionsLogBean"
		entity-name="currentTransactionsLogBean">

<!-- tried to just retrieve the list of Ids so I could perform an 'in' -->

		<subselect>
			select tl.* from dbo.TransactionsLog tl
			inner join (select transactionid, max(modificationDate) as
			modificationDate from dbo.TransactionsLog group by
			transactionId) v on v.modificationDate = tl.modificationDate
			and v.transactionId = tl.transactionId
		</subselect>
		<synchronize table="TransactionsLog" />
		<id name="transactionsLogId" />
	</class>
	
	<!-- this is the standard mapping I'm using (works fine) -->	
	<class
		name="com.harrisalternatives.struts.research.transaction.TransactionsLogBean"
		entity-name="TransactionsLog" table="TransactionsLog">

		<id name="id">
			<column name="TransactionsLogId" />
			<generator class="native" />
		</id>

		<property name="typeId" />

		<property name="investmentDate">
			<column name="investDate" />
		</property>
		

		<!-- other props, etc. -->

		<many-to-one name="transaction" column="transactionId"
			not-null="true" entity-name="Transaction" />
			
			
		<!-- more props, etc. -->
	</class>	
</hibernate-mapping>

Can someone help understand how to use this subselect - and will it even do what I want it to?

Thank you in advance.

[Javid Jamae]

This may not be the answer you're looking for, but I always use a view in a situation like this.

[Fletcher Munson]

That's what I'm trying to do. Only I don't have access to the DB to create one. Hibernate was supposed to have a way to create one using the subselect tag in the mapping file. Only problem is there isn't much documentation, and I can't seem to figure it out.