Hibernate criteria, distinct association property

Question

Say I have at least these two entities:

class Person {
   String firstname, lastname;

   Address address;

   ManyOtherPropertiesAndEntities ...;
}

class Address {
      String street;
      Country country;   
}

Now, I would like to query the Person table and ONLY Persons that live on different streets.

That is, ignore all Persons that live on same street, and return only one of these Person, any one.

How can I perform such a query?

Is that possibly using Criteria?

 Criteria criteria = session.createCriteria(Person.class, "person");

 Criteria addressCriteria = criteria.createCriteria("address")

 criteria.setProjection(
                    Projections.distinct(
                        Projections.projectionList().add(Projections.property("address.street"))
                    )
            );

This doesnt really work.

I've also tried to do:

projectionList.add( Projections.sqlProjection("DISTINCT ON ( address.street ), ... ", columns.toArray(new String[columns.size()]), types.toArray(new Type[types.size()])));

But also fruitless.

>>>>>>>>>>>>>>>>>>>>>>>EDIT<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

I was able to get this to run, and generate an SQL query that actually returns results in a pure sql mode, but seems to return zero in hibernate:

List<String> columns = Lists.lst();
                                        List<Type> types     = Lists.lst();

                                        bondCriteria.setProjection(
                                                Projections.sqlProjection ("DISTINCT ON ( tvmux2_.polarization ) * "
                                                        , columns.toArray (new String[columns.size()])
                                                        , types.toArray   (new Type[types.size()])
                                                )

                                                // Projections.projectionList().add(Projections.distinct(Projections.property("polarization")))
                                        );

                                        ResultTransformer resultTransformer = new ResultTransformer() {
                                                @Override
                                                public List transformList( List collection ) {
                                                        return null;
                                                }

                                                @Override
                                                public Object transformTuple( Object[] tuple, String[] aliases ) {
                                                        return null;
                                                }
                                        };

                                        bondCriteria.setResultTransformer(resultTransformer);

* ITS 2017 and SO still hasn't included a proper editor to easily be able to format code so that indentation and copy and paste is not a complete hell. Feel free to scroll horizontally. *

THis generates teh following query basically

select DISTINCT ON ( tvmux2_.polarization ) *  from TvChannelBond this_ inner join TvChannel tvchannel1_ on this_.channel=tvchannel1_.id inner join TvMux tvmux2_ on this_.mux=tvmux2_.id where this_.enabled=true order by tvmux2_.polarization asc limit 100

which does return results in a non hibernate mode.

However, since sqlProjection method requires the supplementation of 3 params, I am not sure what to add to the second and third params. Types can not be other than predefined Hibernate types just as DOUBLE, STRING and so on.

When debugging into the resultTransformer, it gets into transformTuple with zero length tuple[] and aliases[].

Might have to do with the sqlProjection zero length types and columns lists.

Answer 1

In SQL, you could do it like this:

SELECT p.*
FROM Address a
INNER JOIN Person p ON ...
GROUP BY a.Street
HAVING p.id = MIN(p.id)

This statement selects for every distinct Street from Address the Person with the minimum id value. Instead of MIN(p.id) you can of course use any other field and aggregate function which will match exactly one person per street; MAX(p.id) will work, MIN(p.lastname) won't if there can be more than one "Smith" in a street.

Can you transform the above SQL to your Criteria query?