NH-1426 - Group By Property without adding it to the select clause

[nhibernate-bot]

torkelo created an issue — 4th August 2008, 7:57:29:

It would be good to be able to specify a group by without having the property added the select clause.

For example:

var subquery = DetachedCriteria.For<changeItem>("c1")
    .SetProjection(Projections.ProjectionList()                       
                       .Add(Projections.Max("Revision"))
                       .Add(Projections.GroupProperty("BasePath")))
                       
var items = DetachedCriteria.For<ChangeItem>("c2")
      .Add(Subqueries.EqProperty("Revision", subquery));

This will not work as the GroupProperty will add the property to the select list and doing a property equality on a subquery requires a single column being projected from the subquery.

For this to be implemented I think a new property on the IProjection interface is required, for example a GroupOnly property, the ToSqlString function on the ProjectionsList class can then look at this property and skip group only projections. A new overload for the Projections.GroupProperty would also be needed, for example GroupProperty(string propertyName, bool groupOnly).

If someone thinks this is a good idea I can write a patch for it.

---

Tuna Toksoz added a comment — 10th October 2008, 11:19:58:

Torkel,

what is the purpose of this

var items = DetachedCriteria.For<ChangeItem>("c2")
      .Add(Subqueries.EqProperty("Revision", subquery)); 

---

ayenderahien added a comment — 10th October 2008, 11:59:02:

Tuna, This is a pretty common scenario when w are doing complex query composition, something like this: http://ayende.com/Blog/archive/2007/12/23/NHiberante-Querying-Many-To-Many-associations-using-the-Criteria-API.aspx

---

Tuna Toksoz added a comment — 10th October 2008, 12:04:36:

Ah now I see, i thought a different think. I think I can handle that, or maybe Torkel can do it for us? I'll check if this is not yet fixed and see what i can.

---

torkelo added a comment — 10th October 2008, 12:17:11:

Another option would if one could join on subqueries and not only on relations.

For example:

var subquery = DetachedCriteria.For<changeItem>("c1")
    .SetProjection(Projections.ProjectionList()
                       .Add(Projections.Max("Revision"))
                       .Add(Projections.GroupProperty("BasePath")))
                       
var items = DetachedCriteria.For<ChangeItem>("c2")
                               .InnerJoin(subQuery, "c1.Revision", "c2.Revision");

---

Tuna Toksoz added a comment — 10th October 2008, 12:20:41:

I don't know exactly if this could be a "hard" job, because I remember we needed this functionality in Linq but didn't implement it. Do you have an implementation for this, or it is a sample code?

---

ayenderahien added a comment — 10th October 2008, 12:24:49:

Torkel, That looks lovely. Would you be able to submit a patch?

---

torkelo added a comment — 10th October 2008, 13:10:37:

The innerJoin was just what I would wish existed, I guess I must study the how nhibernate handles relation joins first, I am not that familiar with the nhibernate query internals :)

Ayende would a join on a subquery be difficult to implement?

---

ayenderahien added a comment — 10th October 2008, 13:21:07:

It will probably not be trivial, but it is likely to be possible.

---

Kenneth Siewers Møller added a comment — 5th November 2008, 5:44:25:

Is this possible in NH 2.0.1 GA?

---

Tuna Toksoz added a comment — 5th November 2008, 7:58:27:

Nope, neither in 2.0 nor in 2.1

---

Kenneth Siewers Møller added a comment — 11th December 2008, 2:59:14:

Is there any news on this issue?

---

Tuna Toksoz added a comment — 11th December 2008, 3:11:18:

Nope.

---

ifesdjeen added a comment — 18th August 2009, 12:46:05:

Hi! I've dealt with the same issue just recently during my work. I can take it on myself. Can anyone of experienced guys help out at least a bit (sorry my first time here)? thx

---

Tuna Toksoz added a comment — 18th August 2009, 12:52:44:

If you're experienced with SVN http://tunatoksoz.com/post/Contributing-to-OSS-e28093-Creating-a-test-case-patch.aspx

If you are not

http://nhforge.org/blogs/nhibernate/archive/2008/10/04/the-best-way-to-solve-nhibernate-bugs-submit-good-unit-test.aspx

---

ifesdjeen added a comment — 25th August 2009, 8:50:01:

Actually, it's possible, why not:

DetachedCriteria dc = DetachedCriteria.For(typeof(Student))
					.Add(Property.ForName("StudentNumber").Eq(232L))
					.SetMaxResults(1)
					.AddOrder(Order.Asc("Name"))
					.SetProjection(Property.ForName("Name"));

you can set OrderBy, why can't you? Can anyone give an example of the case when you can't?

---

torkelo added a comment — 25th August 2009, 9:18:09:

@Oleksandr

The issue concerns Group By not Order By :)

---

ifesdjeen added a comment — 25th August 2009, 11:11:53:

gosh... sorry Torkel. i should sleep a bit more. i will investigate this issue deeper tomorrow.

---

Kenneth Siewers Møller added a comment — 7th January 2010, 2:51:32:

Are there any plans on fixing this issue?

---

ifesdjeen added a comment — 7th January 2010, 2:55:53:

If there's someone else, i wouldn't mind. If not - i'm almost done with my primary project, so i will continue in about next week.

---

Carina Méndez Rodríguez added a comment — 5th February 2010, 4:59:48:

Hi, I'm new here. But I'm using NHibernate since quite a long time and now got the moment when I need exactly this funcionality. My code for the moment is the following:

DetachedCriteria query = DetachedCriteria.For(typeof("type1"));
DetachedCriteria subquery = DetachedCriteria.For(typeof("type2"));

subquery.SetProjection(Projections.ProjectionList()
        .Add(Projections.Max("Id"))
        .Add(Projections.GroupProperty("grouping_field"))
);
query.Add(Subqueries.PropertyIn("Id", subquery));

But the problem is the one you guys just mentioned previously. The subquery returns both Id en the grouping_field.

Dou you already have a solution for this problem? Would be quite nice :) Otherwise I will have to go back to HQL :|

Thanks in advance.

---

Andrea Montemaggio added a comment — 17th September 2011, 15:56:44:

Here is another use case where I need this feature. I'm trying to add dynamic ordering for queries built by criteria API, and when I detect a "GROUP BY" clause in the root criteria I need to add all ordering fields to the "ORDER BY" clause and the "GROUP BY" clause (really "paths" because may be from joined classes also). The trouble is that adding to the "GROUP BY" clause produces a new projection, so a call to "List<T>" throws an exception: "System.ArgumentException: The value "System.Object[]" is not of type "T" and cannot be used in this generic collection.".

---

[email protected] added a comment — 12th July 2013, 17:29:11:

Hi all. Just to be sure, there is a workaround for this using QueryOver or I must rewrite my query with native sql?

---

Peter van der Woude added a comment — 26th September 2017, 5:47:18:

Hi, this QueryOver query (see below) has the exact same problem described - the GROUPBY field is added to the SELECT clause. Which means it fails to run, throwing an exception - Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

So just wanting to point out it's not just a problem for the Criteria API (as this issue is labelled) but also the QueryOver API

Unless there is a workaround, I would be very glad to have it ;-)

var maxSubquery = QueryOver.Of<Report>()
.SelectList(l => l
	.SelectGroup(_ => _.Publisher)
	.SelectMax(_ => _.Version)
).Where(_ => _.IsPrivate);

var query = Session.QueryOver<Report>();
query.WithSubquery
.WhereProperty(r => r.Version)
.In(maxSubquery);

return query.List();

[mihaicodrean]

Is anyone interested in fixing this? @tunatoksoz was suggesting the following in NH-1489 (the duplicate issue):

Maybe we could add a GroupBy thing to Criteria and do the reverse? If property is grouponly it won't be added to projection list, if not it will.

[BackGrounder84]

I have exact this problem. A fix would be nice.

[psavard]

The same problem just happened to me. Any news on this issue?

[hazzik]

@psavard, as far as I know, no one work on this. You are more than welcome to submit a PR. See contributing.

[psavard]

Finally, I've been able to work around this problem by using a view as my subquery. Maybe not the best but it works fine and I don't have more time to loose on this one.