CODEWITHSUNDEEP
c#mysqlnhibernategroup-byqueryover
aron23
aron23

Reputation: 323

NHibernate SQLFunction group by

I am trying to do something like this:

SELECT round(song.rating), count(song.song_id) FROM song
GROUP BY round(song.rating);

My QueryOver:

 var output = sess.QueryOver<song>()
                .SelectList(list => list
                    .Select(Projections.SqlFunction("round", NHibernateUtil.Int32, Projections.GroupProperty("rating")))
                    .SelectCount(s => s.song_id))
                .List<object[]>()
                .Select(prop => new RatingStat
                {
                    rating = (int)prop[0],
                    count = (int)prop[1]
                }).ToList<RatingStat>();

Expected output:

+---------------------------+---------------------------+
|                         0 |                        12 |
|                         1 |                         1 |
|                         3 |                         1 |
|                         4 |                         6 |
|                         5 |                         3 |
|                         6 |                         6 |
|                         7 |                        12 |
|                         8 |                         7 |
|                         9 |                         9 |
|                        10 |                         2 |
+---------------------------+---------------------------+

Actual output:

0                         12
1                         1
3                         1
4                         1
4                         3
4                         1
4                         1
5                         1
5                         1
5                         1
6                         2
6                         1
6                         3
7                         2
7                         9
7                         1
8                         1
8                         3
8                         2
8                         1
9                         1
9                         3
9                         1
9                         4
10                        2

I am using my own dialect inherited from MySQL5Dialect, because my MySQL dialect doesnt support round function. Here is how round function is defined in my dialect:

 RegisterFunction("round", new StandardSafeSQLFunction("round", NHibernateUtil.Int32,1));

My question is, why do I have multiple groups of same rating value? Rounded values should be distinct. Is it possible that round function doesnt work correctly? Edit: added genetated SQL statement

 SELECT round(this_.rating) as y0_, count(this_.song_ID) as y1_ FROM song this_ GROUP BY this_.rating

Upvotes: 1

Views: 479

Answers (1)

aron23
aron23

Reputation: 323

found solution:

 var t = Projections.SqlFunction("round", NHibernateUtil.Int32, Projections.GroupProperty("rating"));
 var output = sess.QueryOver<Song>()
       .SelectList(list => list
       .Select(Projections.SqlFunction("round", NHibernateUtil.Int32, Projections.GroupProperty(t)))
       .SelectCount(s => s.song_id))
       .List<object[]>()
       .Select(prop => new RatingStat
        {
            rating = (int)prop[0],
            count = (int)prop[1]
        }).ToList<RatingStat>();

Upvotes: 3

Related Questions