I have a query that did work fine in Nhibernate 1.0 that I've recently converted to NHibernate 1.2 GA due to our move to .NET 2.0.

The query is :

select distinct e from CalendarEvent e, Performance as p where e.EventId = p.EventId and (p.PerformanceDate >= ? and p.PerformanceDate <= ? ) order by p.PerformanceDate


I get an exception :
"ORDER BY items must appear in the select list if SELECT DISTINCT is specified."} System.Data.SqlClient.SqlException

So it wants me to select the p.PerformanceDate in the result set, which I don't want, I just want the events that meet those dates via their join to the performances.

I had this issue in a very early version of NHibernate that was fixed in later releases, but it seems to have come back.

Since this seems to be a fairly straightforward translation of HQL to SQL, and is apparently illegal, I'm wondering what SQL the earlier NH versions generated.

It makes sense that this wouldn't be legal, since there is no guarantee that there are not multiple non-distinct dates for each distinct column-set you are selecting. Maybe the previous versions "silently" added the order by columns to the select list, but that could have caused some pretty confusing failures ("why am I getting back multiple references to the same object in my list when I specify DISTINCT...?"), which may be why the behavior was abandoned.

If that was the case, you could just add the date to the select list, which should then return a list of duples, of which the prior of each will be the CalendarEvent in question.

So is there a more correct way to write my query ? Sorry I'm a developer more than a sql guy :)

Events are joined to performances via the event id, so there is many performances per event. I want all the events between two dates, and no duplicates.

I guess I could select the CalendarEvent and the performanceDate and order by performance date, but then I'd have to run through the returned list of results grabbing the first element of each item (CalendarEvent) and putting them into a new list to return to the caller, since all I want is CalendarEvent objects...

e.g

CalendarEvent

eid
name

Performance
id
eid
date

performance is joined to Calendar event via the eid pk and fk. and is a 1:M relationship.


Grant

Consider: CalenderEvents (CE) 1 & 2, with Performances:

1: 4/2, 4/30
2: 4/15, 4/22

and you select all the calender events for performances in April. How would you propose ordering them by date? It doesn't make any sense. Drop the ordering. I have a feeling that is what NH was doing behind the scenes before, anyway.

I think the query needs to be a bit more complex and reversed.

Performance contains the event id as a foreign key so I could in traditional sql do :

select p.eid from performance p where (p.date> ? and p.date<?) orderby p.date

This would give me the event id's that the performance have that fall in those two date ranges. Now I want to actuall get the events themselves and thats were I get stuck :)

Grant

Here is some rough sql, but I don't know how I get distinct EID's

SELECT distinct (eid), date

FROM dbo.PERFORMANCES

WHERE (date > CONVERT(DATETIME, '2007-08-01 00:00:00', 102)) AND (date < CONVERT(DATETIME, '2007-08-31 00:00:00', 102))

ORDER BY date


This returns me all eids between those dates, but of course I get duplicates. I want distinct eids from this, I'm sure I'm suppose to do an inner query or something but I'm not exactly hot on sql :)

Grant

Got the query to give me distinct event ids, now wondering how I'd return the CalendarEvent in HQL with something similar...

SELECT eid, max( date ) as date
FROM dbo.PERFORMANCES
WHERE (date > CONVERT(DATETIME, '2007-08-01 00:00:00', 102)) AND (date < CONVERT(DATETIME, '2007-08-31 00:00:00', 102))
group by eid order by date

It wasn't really a SQL issue: the problem was that you cannot logically order a distinct set of entities by a set of values of which each entity may have several. Translating: each CalendarEvent has several performance dates, so the ordering is impossible.

Unless, of course, you choose one date, which is what you are getting at with your last query where it seems that you are attempting to order by the last (max) performance date. Now we're getting somewhere.

This is possible: see here. I think what you want can be acheived like so:



There may also be a way to do this with a joined subquery so that you don't have to list all the properties of CalendarEvent in your group by clause.