subquery - How can I recreate this complex SQL Query using NHibernate QueryOver?

Question

Imagine the following (simplified) database layout:

We have many "holiday" records that relate to going to a particular Accommodation on a certain date etc.

I would like to pull from the database the "best" holiday going to each accommodation (i.e. lowest price), given a set of search criteria (e.g. duration, departure airport etc).

There will be multiple records with the same price, so then we need to choose by offer saving (descending), then by departure date ascending.

I can write SQL to do this that looks like this (I'm not saying this is necessarily the most optimal way):

SELECT *
FROM Holiday h1 INNER JOIN (

    SELECT  h2.HolidayID,
        h2.AccommodationID,
        ROW_NUMBER() OVER (
            PARTITION BY h2.AccommodationID
            ORDER BY OfferSaving DESC
        ) AS RowNum
    FROM Holiday h2 INNER JOIN (

        SELECT  AccommodationID,
            MIN(price) as MinPrice
        FROM Holiday
        WHERE TradeNameID = 58001
        /*** Other Criteria Here ***/
        GROUP BY AccommodationID

    ) mp
    ON mp.AccommodationID = h2.AccommodationID
    AND mp.MinPrice = h2.price
    WHERE TradeNameID = 58001
    /*** Other Criteria Here ***/

) x on h1.HolidayID = x.HolidayID and x.RowNum = 1

As you can see, this uses a subquery within another subquery.

However, for several reasons my preference would be to achieve this same result in NHibernate.

Ideally, this would be done with QueryOver - the reason being that I build up the search criteria dynamically and this is much easier with QueryOver's fluent interface. (I had started out hoping to use NHibernate Linq, but unfortunately it's not mature enough).

After a lot of effort (being a relative newbie to NHibernate) I was able to re-create the very inner query that fetches all accommodations and their min price.

public IEnumerable<HolidaySearchDataDto> CriteriaFindAccommodationFromPricesForOffers(IEnumerable<IHolidayFilter<PackageHoliday>> filters, int skip, int take, out bool hasMore)
    {
        IQueryOver<PackageHoliday, PackageHoliday> queryable = NHibernateSession.CurrentFor(NHibernateSession.DefaultFactoryKey).QueryOver<PackageHoliday>();

        queryable = queryable.Where(h => h.TradeNameId == website.TradeNameID);

        var accommodation = Null<Accommodation>();
        var accommodationUnit = Null<AccommodationUnit>();
        var dto = Null<HolidaySearchDataDto>();

        // Apply search criteria
        foreach (var filter in filters)
            queryable = filter.ApplyFilter(queryable, accommodationUnit, accommodation);

        var query1 = queryable

            .JoinQueryOver(h => h.AccommodationUnit, () => accommodationUnit)
            .JoinQueryOver(h => h.Accommodation, () => accommodation)
            .SelectList(hols => hols
                                    .SelectGroup(() => accommodation.Id).WithAlias(() => dto.AccommodationId)
                                    .SelectMin(h => h.Price).WithAlias(() => dto.Price)
            );

        var list = query1.OrderByAlias(() => dto.Price).Asc
            .Skip(skip).Take(take+1)
            .Cacheable().CacheMode(CacheMode.Normal).List<object[]>();

        // Cacheing doesn't work this way...
        /*.TransformUsing(Transformers.AliasToBean<HolidaySearchDataDto>())
        .Cacheable().CacheMode(CacheMode.Normal).List<HolidaySearchDataDto>();*/

        hasMore = list.Count() == take;

        var dtos = list.Take(take).Select(h => new HolidaySearchDataDto
                    {
                        AccommodationId = (string)h[0],
                        Price = (decimal)h[1],
                    });

        return dtos;
    }

So my question is...

Any ideas on how to achieve what I want using QueryOver, or if necessary Criteria API?

I'd prefer not to use HQL but if it is necessary than I'm willing to see how it can be done with that too (it makes it harder (or more messy) to build up the search criteria though).

If this just isn't doable using NHibernate, then I could use a SQL query. In which case, my question is can the SQL be improved/optimised?

question from:https://stackoverflow.com/questions/5166303/how-can-i-recreate-this-complex-sql-query-using-nhibernate-queryover

Answer 1

I have manage to achieve such dynamic search criterion by using Criteria API's. Problem I ran into was duplicates with inner and outer joins and especially related to sorting and pagination, and I had to resort to using 2 queries, 1st query for restriction and using the result of 1st query as 'in' clause in 2nd creteria.