Hi,

I have this entity: ChangeItem { Id, Path, Revision }

I would like to do this sql query:

select * from ChangeItems c1
inner join (
select Path, Max(Revision) as Revision from ChangeItems c2
Group by Path
) c2 on c1.Path=c2.Path and c1.Revision=c2.Revision

Basically select all ChangeItems of the highest revision for a path.

To define the inner query was no problem:

var innerQuery = DetachedCriteria.For<ChangeItem>("c2")
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property("Path"))
.Add(Projections.Max("Revision"))
.Add(Projections.GroupProperty("Path")))

But how to a define a new critiera and then join that with the innerQuery? I have been googling and browseing through the nhibernate source and test cases and I am a little unsure if it is possible.

I was hoping do do something like this:

var items = DetachedCriteria.For<ChangeItem>("c1")
.CreateCriteria(innerQuery)
.Add(Property.ForName("Path).EqProperty("c2.Path")
.Add(Property.ForName("Revision).EqProperty("c2.Revision")

Anyone know if it is possible in nhibernate to join to subquery? To me it looks like it has to be a association.

/Torkel

You can try this:

Thanks for the replay, I do not think that will result in the same thing though, but I will try it.

Nope :(

That query does not return the correct result :(

I guess I have to use a native SQL Query for this one. But I am having trouble getting it to work, it seams like you can not use CreateSQLQuery for entities that contain formula properties. Even when you include the formula and name it exactly like the one generated by NHibernate.

You get a expection:

System.ArgumentNullException: Value cannot be null.
Parameter name: fieldName
at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)

It seems like the loading is passing in null for the formula property, this problem has been reported in this thread: http://forum.hibernate.org/viewtopic.php?p=2374703

That's what the criteria I posted should do.



What's the difference between the result the criteria has and what you need ?

For the subquery I need to group by path, and it does not seem to be possible to do a group by without projecting the grouped by property. So if I add a Group By projection and the Max(Revision) projection I can no longer use the Subqueries.PropertyEq method as the subquery returns more than one value.

Example of data:
Rev Path
1 /trunk/file.txt
2 /trunk/file.txt

I want to fetch the rows with the latest revision, for a specific path. In theory it would work with SubQueries.EqProperty

for example this sql query works:
select * from ChangeItems c1 where c1.Revision = (select Max(Revision) from ChangeItems c2 where c1.Path=c2.Path group by Path)

But I don't know how to do this query with nhibernate as a group by will add the grouped by property to the projection list.

I can't see why you need the group by ? With "c1.Path=c2.Path" you restrict the result set to a single path and there's no reason for grouping then.

Sorry, I guess I simplified the scenario a little to much :)

I actually have this data:
Rev Path BasePath

I need to fetch all the latest (revisions) for a specific BasePath, so I need to group by path. Sorry for not including this important detail!

Hmmm .... I see. You can try this, but I'm not sure if it works.

You get a SqlException:

"Subquery returned more than 1 value"

Thanks for you help, I think I will take this up on the nh dev mailing list, see if there is change to either add the possibility to join to a subquery or do a group by without including the grouped by column in the select clause.

I think both these features should be good to have and at least the second one should not be to difficult to implement.

Subqueries.PropertyEq isn't working anymore, since now there can be more then one row in the resultset of the query. It has to be Subqueries.PropertyIn ...

Since the subquery selects MAX(Revision) and Path I do not think an IN clause will work.

I hoped that

var items = session.CreateCriteria(typeof(ChangeItem), "c1")
.Add(Subqueries.PropertyEq("c1.Revision", subquery.SetProjection(Projections.Property("Revision"))
.List();

would have solved that ... but open a JIRA issue anyway, I agree with you, that grouping without projecting the property would be quite helpful.

Yea, I hoped that too :)

I will open up a JIRA, and maybe have a look at doing a patch myself if I get the time.