Критерии NHibernate Внутреннее объединение SQL в подпрограмме Select Same Table

Я не могу, хоть убей, понять, как перевести следующий SQL-запрос с помощью API критериев NHibernate:

SELECT r.* from ContentItemVersionRecords as r
    INNER JOIN (
        SELECT ContentItemId as CID, Max(Number) as [Version]
        FROM ContentItemVersionRecords
        GROUP BY ContentItemId
    ) AS l
ON r.ContentItemId = l.CID and r.Number = l.[Version]
WHERE Latest = 0 and Published = 0

Таблица выглядит так:

возвращаются последние версии

Результат SQL-запроса выше вернет выделенные записи. Идея состоит в том, чтобы выбрать последнюю версию элементов контента, поэтому мне в основном нужно сгруппировать по ContentItemId и получить запись с наибольшим числом.

Результат будет таким:

возвращены последние версии

Я начал с обособленных критериев, но не знаю, как использовать их в критериях:

// Sub select for the inner join:
var innerJoin = DetachedCriteria.For<ContentItemVersionRecord>()
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.GroupProperty("ContentItemId"), "CID")
        .Add(Projections.Max("Number"), "Version"));

// What next?
var criteria = session.CreateCriteria<ContentItemVersionRecord>();

Обратите внимание, что мне нужно использовать Criteria API - я не могу использовать LINQ, HQL или SQL. Возможно ли это вообще с API критериев?

ОБНОВЛЕНИЕ: я только что наткнулся на этот пост, который очень похоже на мой вопрос. Однако, когда я применяю это следующим образом:

var criteria = session
    .CreateCriteria<ContentItemVersionRecord>()
    .SetProjection(
        Projections.ProjectionList()
            .Add(Projections.GroupProperty("ContentItemId"))
            .Add(Projections.Max("Number")))
    .SetResultTransformer(Transformers.AliasToBean<ContentItemVersionRecord>());

Я получаю 2 результата, которые выглядят многообещающими, но все целочисленные свойства равны 0:

введите описание изображения здесь

ОБНОВЛЕНИЕ 2: я обнаружил, что если я предоставлю псевдонимы, он будет работать (то есть я получу список ContentItemVersionRecords с заполненными объектами):

var criteria = session
    .CreateCriteria<ContentItemVersionRecord>()
    .SetProjection(
        Projections.ProjectionList()
            .Add(Projections.Max("Id"), "Id")
            .Add(Projections.GroupProperty("ContentItemId"), "ContentItemId")
            .Add(Projections.Max("Number"), "Number"))
    .SetResultTransformer(Transformers.AliasToBean<ContentItemVersionRecord>());

Однако я не могу использовать прогнозируемые значения в качестве конечного результата - мне нужно использовать эти результаты как своего рода ввод во внешний запрос, например

SELECT * FROM ContentItemVersionRecord WHERE Id IN ('list of record ids as a result from the projection / subquery / inner join')

Но это не сработает, поскольку проекция возвращает 3 скалярных значения (Id, ContentItemId и Number). Если бы он просто вернул «Id», то это могло бы сработать. Но мне нужно, чтобы две другие проекции были сгруппированы по ContentItemId и упорядочены по Max ("Number").


sql-server inner-join criteria nhibernate
person Sipke Schoorstra    schedule 24.10.2014    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Итак, вкратце, вам нужно развернуть этот вложенный запрос и создать группу с помощью предложения Have, которое в значительной степени похоже на место для агрегированных значений, как в следующем HQL:

SELECT civ.ContentItem.Id, MAX(civ.Number) AS VersionNumber
FROM ContentItemVersionRecord civ
JOIN ContentItem ci
GROUP BY civ.ContentItem.Id " +
HAVING MAX(civ.Latest) = 0 AND MAX(civ.Published) = 0

Это дает вам для каждого удаленного элемента контента (у которых все последние и опубликованные флаги установлены на ноль во всех записях версий элементов контента) максимальный номер версии, то есть последнюю версию каждого удаленного элемента контента.

person Bertrand Le Roy    schedule 25.10.2014
comment
На данный момент я отказался от этого с Criteria API и пытался преобразовать свой SQL-запрос в HQL. Ваш подход намного чище (и он работает). Я попробовал, и он дал те результаты, которые мне были нужны, поэтому, хотя я не получил ответа с помощью Criteria API, я все равно приму ваш ответ, поскольку он помог мне решить мою проблему. Спасибо!! - person Sipke Schoorstra; 26.10.2014