NHibernate "SELECT ... FROM (SELECT ..." Critères

Question

J'ai une requête SQL que j'ai du mal à créer à l'aide de NHibernate Criteria :

SELECT ID, COLA, COLB, COLC
FROM table
WHERE COLC='something' AND ID IN (SELECT ID FROM (SELECT MAX(ID) as ID, COLA FROM table WHERE COLB='something' GROUP BY COLA) subquery)
ORDER BY ID DESC

À l'origine, j'avais une question un peu plus simple :

SELECT ID, COLA, COLB, COLC
FROM table
WHERE COLC='something' AND ID IN (SELECT MAX(ID) FROM table WHERE COLB='something' GROUP BY COLA)
ORDER BY ID DESC

Cependant, avec NHibernate, si j'utilise un "GROUP BY", il ajoute automatiquement le champ à l'instruction SELECT, et je n'ai aucun moyen de l'arrêter (pour autant que je sache).

En gros, j'ai besoin de trouver le dernier enregistrement groupé par une colonne arbitraire (dans cet exemple, "COLA"). Je sélectionne l'ID maximum pour essayer d'obtenir le dernier enregistrement (mais cela pourrait être autre chose, comme "MAX(UPDATED)"). Après avoir obtenu l'ensemble des derniers enregistrements, je les filtre à nouveau ("WHERE COLC='quelque chose'"), et je sélectionne les colonnes dont j'ai besoin dans le résultat.

S'il existe un meilleur moyen d'obtenir les mêmes résultats, je serais heureux de l'entendre. Mes compétences en SQL sont médiocres, au mieux.

Dans NHibernate, j'ai pu obtenir les deux requêtes correctement, mais la partie du milieu - "SELECT ID FROM" - ne fonctionnait pas.

La requête principale :

DetachedCriteria.For<table>()
    .Add<table>(x => x.COLC == "something")
    .Add(LambdaSubquery.Property<table>(x => x.ID).In(subquery));

Et la sous-requête :

DetachedCriteria.For<table>()
    .Add<table>(x => x.COLB == "something")
    .SetProjection(Projections.ProjectionList()
        .Add(LambdaProjection.Max<table>(x => x.ID))
        .Add(LambdaProjection.GroupProperty<table>(x => x.COLA)));

Le critère de la sous-requête place "COLA" dans la liste de sélection (à cause de la GroupProperty), il est donc inutilisable seul, et c'est pourquoi je dois trouver comment faire le "SELECT ID FROM (SELECT ..." dans les critères. Une fois combinées, elles produisent le SQL invalide suivant (parce que la sous-requête renvoie plus d'une colonne) :

SELECT ID, COLA, COLB, COLC
FROM table
WHERE COLC='something' AND ID IN (SELECT MAX(ID), COLA FROM table WHERE COLB='something' GROUP BY COLA)
ORDER BY ID DESC

Edit : Cela pourrait également permettre de voir le type de données et de résultats que je souhaite obtenir :

ID  COLA  COLB          COLC
1   1     someone       someother
2   1     something     someone     (Matches subquery, but not the max. ID)
3   1     something     something   (Matches subquery and main query)
4   2     someone       something
5   2     something     someother   (Only matches subquery)
6   3     someone       someother

Le résultat que je veux ici est l'ID maximum pour un ensemble donné de "COLA", où "COLB" correspond à "quelque chose", donc je voudrais que la sous-requête renvoie {3, 5}. Au final, la requête ne renvoie que l'enregistrement correspondant à l'ID 3 (la clause WHERE externe élimine le 5 car COLC est erroné). Les données réelles dans COLB et COLC ne sont pas pertinentes - je les utilise simplement pour filtrer davantage les résultats.

Je pense qu'au fond, je veux le dernier enregistrement (ID maximum) pour chaque série de COLA.

SELECT ID, COLA, COLB
FROM table
WHERE ID IN (SELECT MAX(ID) FROM table GROUP BY COLA)
ORDER BY ID DESC

Answer 1

Donc, en supposant que vous recherchez le ou les enregistrements qui ont l'ID maximum avec la valeur de COLA et qui ont également COLB avec l'ID MAX correspondant, vous pourriez utiliser quelque chose comme ceci.

var test =
                ActiveRecordLinq.AsQueryable<table>().Where(
                    y =>
                    y.COLB == "Something" &&
                    y.ID == (from x in ActiveRecordLinq.AsQueryable<table>()
                                   where x.COLA == "Something"
                                   select x).Max(z => z.ID))

J'ai essayé en utilisant certains de mes objets et données de test et cela semble fonctionner. Faites-moi savoir comment cela se passe.

EDIT

Pour obtenir la liste des ID maximaux avec COLA, vous pouvez utiliser cette requête. Je vais d'abord vous montrer celle que j'ai utilisée pour mes tests, puis ce à quoi la vôtre devrait ressembler.

J'ai un objet groupe d'e-mails qui contient un titre et un objet TXDX qui contient l'ID sur lequel j'ai effectué ma recherche.

var maxes = (from x in ActiveRecordLinq.AsQueryable<Email_Group>()
                     group x by x.Headline
                     into g
                     select new {COLA = g.Key, MaxID = g.Max(z => z.TXDX.TXDX_ID)});

Le vôtre :

var maxes = (from x in ActiveRecordLinq.AsQueryable<table>()
                     group x by x.COLA
                     into g
                     select new {COLA = g.Key, MaxID = g.Max(z => z.ID)});

Je travaille toujours sur l'ensemble de la requête. J'essaie de la faire en une seule fois. Je publierai un message dès que je l'aurai, mais nous sommes vendredi et je fais ça entre deux travaux.

Answer 2

J'ai trouvé une solution alternative puisque je n'arrivais pas à comprendre comment obtenir les bons résultats en utilisant l'API de critères dans NHibernate - utiliser une vue à la place.

La vue utilise simplement la requête que j'avais initialement prévue (sans certains des filtres supplémentaires) :

SELECT ID, COLA, COLB, COLC, ...
FROM table
WHERE ID IN
    (SELECT MAX(ID)
     FROM table
     GROUP BY COLA)

Ensuite, dans NHibernate, j'ai mappé la vue comme une table ordinaire (sauf que je l'ai rendue immuable) :

<class name="View" table="View" lazy="false" mutable="false">
    <id name="ID" type="Int32">
        <generator class="assigned"/>
    </id>
    <property name="COLA" type="String" length="100">
        <column name="COLA" />
    </property>
    <property name="COLB" type="String" length="100">
        <column name="COLB" />
    </property>
    <property name="COLC" type="String" length="100">
        <column name="COLC" />
    </property>
    <!-- Other fields -->
</class>

Et j'ai créé la classe à mettre en correspondance :

public class View
{
    public virtual int ID { get; set; }
    public virtual string COLA { get; set; }
    public virtual string COLB { get; set; }
    public virtual string COLC { get; set; }
    // Other fields
}

Et enfin, j'ai créé une requête pour la vue :

DetachedCriteria.For<View>()
    .Add<View>(x => x.COLB == "something")
    .Add<View>(x => x.COLC == "something")
    // Any other filtering criteria
    .AddOrder<View>(x => x.COLA, Order.Desc);

J'aurais préféré utiliser une requête à critère unique dans NHibernate, mais cela a permis de faire le travail.