Filter a criteria query by a subquery attribute
<p>I'm trying to implement a criteria a bit complex.<br>
My first query was this.</p>

<pre><code>SELECT * FROM pin AS activepin
WHERE activepin.pin_nbr NOT IN 
( 
 SELECT p.pin_nbr 
 FROM pin AS p 
 GROUP BY p.pin_nbr,p.is_active 
 HAVING p.is_active = 0 
 AND count(*) =( 
    SELECT count(*) 
    FROM pin 
    WHERE pin_nbr = p.pin_nbr 
    GROUP BY pin_nbr)
 );
</code></pre>

<p>so, in order to convert having to where clause (i think that having and criteria are not good friends xD) i converted it in this one:</p>

<pre><code>SELECT *
FROM pin AS activePin
WHERE activePin.pin_nbr NOT IN (
    SELECT innerQuery2.pin_nbr
    FROM(
        SELECT p.pin_nbr,
               p.is_active,
               count(*) AS quantity
        FROM pin p
        GROUP BY p.pin_nbr,
                 p.is_active) AS innerQuery2
     WHERE innerQuery2.is_active = 0
     AND innerQuery2.quantity =(
         SELECT count(*)
         FROM pin
         WHERE pin_nbr = innerQuery2.pin_nbr
         GROUP BY pin_nbr
         )
 );
</code></pre>

<p>and then i did this criteria</p>

<pre><code>        DetachedCriteria innerQuery3 = DetachedCriteria.forClass(Pin.class, "innerQuery3")
                .setProjection(Projections.rowCount())
                .setProjection(Projections.groupProperty("innerQuery3.pinNbr"))
                .add(Restrictions.eq("innerQuery2.pinNbr", "pinNbr"));

        DetachedCriteria innerQuery2 = DetachedCriteria.forClass(Pin.class, "innerQuery2")
                .setProjection(Projections.rowCount())
                .setProjection(Projections.groupProperty("innerQuery2.pinNbr"))
                .setProjection(Projections.groupProperty("innerQuery2.active"))
                .add(Subqueries.eq(Projections.rowCount(), innerQuery3))
                .add(Restrictions.eq("innerQuery2.pinNbr", "pinNbr"));

        DetachedCriteria innerQuery1 = DetachedCriteria.forClass(Pin.class, "innerQuery1")
                .setProjection(Projections.property("innerQuery2.pinNbr"))
                .add(Subqueries.exists(innerQuery2))
                .add(Restrictions.eq("innerQuery2.active", false));

        criteria.add(Subqueries.propertyNotIn("pinNbr", innerQuery1));
</code></pre>

<p>which throw me the following Exception</p>

<blockquote>
  <p>org.hibernate.MappingException: Unknown entity: null</p>
</blockquote>

<p>I think this happens because i'm adding a Restrictions by a subquery value (i mind, at innerQuery1 i want to filter by a innerQuery2 value, that is because i want to filter after group clause).</p>

<p>Any clue of how this can be done? i've been researching how to filter by a  subquery value outside that subquery with no results.</p>

<p>thanks in advance.</p>