Return single column from multi-group Hibernate projection

Question

What I want to achieve: Select a susbset of entities that have a property value that exists in a List of values. This list is returned by another Query.

In plain SQL, this can be easily achieved with subqueries. This is the Criteria query that returns the relevant values:

DetachedCriteria subq = DetachedCriteria.forClass(MyClass.class)
    .setProjection(
        Projections.projectionList()
            .add(Projections.alias(Projections.max("interestingVal"), "interestingVal"))
            .add(Projections.groupProperty("someval1"))
            .add(Projections.groupProperty("someval2"))
            .add(Projections.groupProperty("someval3"))
        );

I would then like to select the entities that have a value of interesting_val that was returned by the above query. Like so:

List<MyClass> resultList = sessionFactory.getCurrentSession().createCriteria(MyClass.class)
            .add(Subqueries.propertyIn("interestingVal", subq))
            .list();

Unfortunately, the subq subquery returns a List of Object-arrays with length 4, since I have 4 Projection values. All projection values seem to be automatically added to the SELECT clause. This results in an SQLSyntaxErrorException: ORA-00913: too many values.

How can I either tell my second Criteria query to only use the first element in the Object array or only retrieve the first column in my subquery?

Answer 1

Instead of propertyIn try propertiesIn.

String[] vals = new String[]{"interestingVal", "someval1", "someval2", "someval3"};
List<MyClass> resultList = sessionFactory.getCurrentSession().createCriteria(MyClass.class)
            .add(Subqueries.propertiesIn(vals, subq))
            .list();