Comment puis-je recréer cette requête SQL complexe en utilisant NHibernate QueryOver ?

Question

Imaginez la disposition suivante (simplifiée) de la base de données :

Nous avons de nombreux enregistrements de "vacances" qui concernent le fait de se rendre dans un logement particulier à une certaine date, etc.

Je voudrais extraire de la base de données les "meilleures" vacances pour chaque hébergement (c'est-à-dire le prix le plus bas), en fonction d'un ensemble de critères de recherche (par exemple, la durée, l'aéroport de départ, etc.).

Il y aura plusieurs enregistrements avec le même prix, nous devons donc choisir par économie de l'offre (en ordre décroissant), puis par date de départ en ordre croissant.

Je peux écrire du SQL pour faire cela qui ressemble à ceci (je ne dis pas que c'est nécessairement la manière la plus optimale) :

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

Comme vous pouvez le voir, cela utilise une sous-requête dans une autre sous-requête.

Toutefois, pour plusieurs raisons, je préférerais obtenir ce même résultat dans NHibernate.

Idéalement, cela devrait être fait avec QueryOver - la raison étant que je construis les critères de recherche de manière dynamique et que cela est beaucoup plus facile avec l'interface fluide de QueryOver. (J'avais commencé à espérer utiliser NHibernate Linq, mais malheureusement il n'est pas assez mature).

Après beaucoup d'efforts (étant relativement novice en matière de NHibernate), j'ai pu recréer la requête interne qui récupère tous les logements et leur prix minimum.

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

Donc ma question est...

Avez-vous des idées sur la façon d'obtenir ce que je veux en utilisant QueryOver, ou si nécessaire Criteria API ?

Je préférerais ne pas utiliser HQL mais si c'est nécessaire, je suis prêt à voir comment cela peut être fait avec cela aussi (cela rend plus difficile (ou plus désordonné) la construction des critères de recherche).

Si cela n'est pas possible avec NHibernate, je pourrais alors utiliser une requête SQL. Dans ce cas, ma question est la suivante : le SQL peut-il être amélioré/optimisé ?

Answer 1

J'ai réussi à obtenir un tel critère de recherche dynamique en utilisant les API de critères. Le problème que j'ai rencontré est celui des doublons avec les jointures internes et externes, notamment en ce qui concerne le tri et la pagination, et j'ai dû recourir à deux requêtes, la première pour la restriction et l'utilisation du résultat de la première requête comme clause 'in' dans la deuxième.