¿Cómo puedo recrear esta compleja consulta SQL utilizando NHibernate QueryOver?

subquery

Question

Imagine el siguiente diseño de base de datos (simplificado):

Tenemos muchos registros de "vacaciones" relacionados con ir a un Alojamiento en particular en una fecha determinada, etc.

Me gustaría sacar de la base de datos las "mejores" vacaciones para cada alojamiento (es decir, el precio más bajo), dado un conjunto de criterios de búsqueda (por ejemplo, duración, aeropuerto de salida, etc.).

Habrá múltiples registros con el mismo precio, entonces debemos elegir por oferta de ahorro (descendente), luego por fecha de salida ascendente.

Puedo escribir SQL para hacer esto que se ve así (no digo que esta sea necesariamente la forma más óptima):

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

Como puede ver, esto utiliza una subconsulta dentro de otra subconsulta.

Sin embargo, por varias razones, mi preferencia sería lograr el mismo resultado en NHibernate.

Idealmente, esto se haría con QueryOver, la razón es que construyo los criterios de búsqueda dinámicamente y esto es mucho más fácil con la interfaz fluida de QueryOver. (Empecé con la esperanza de utilizar NHibernate Linq, pero desafortunadamente no es lo suficientemente maduro).

Después de mucho esfuerzo (siendo relativamente novato en NHibernate), pude volver a crear la consulta muy interna que busca todas las acomodaciones y su precio mínimo.

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;
    }

Entonces mi pregunta es ...

¿Alguna idea sobre cómo lograr lo que quiero usando QueryOver o, si es necesario, Criteria API?

Preferiría no utilizar HQL, pero si es necesario, no estoy dispuesto a ver cómo se puede hacer con eso (aunque hace que sea más difícil (o más complicado) crear los criterios de búsqueda).

Si esto simplemente no es factible con NHibernate, entonces podría usar una consulta SQL. En ese caso, mi pregunta es si se puede mejorar / optimizar el SQL.

Answer 1

Me las arreglé para lograr dicho criterio de búsqueda dinámico mediante el uso de API de Criteria. El problema con el que me topé fue duplicado con combinaciones internas y externas y especialmente relacionado con la ordenación y la paginación, y tuve que recurrir al uso de 2 consultas, la 1ª consulta para restricción y el uso de la 1ª consulta como cláusula "en" en la 2ª cretería.