Search code examples
javamysqlhibernatecriteria

Distinct function with Criteria

I want to execute this query with Criterias but it seems is not working as expected:

SELECT DISTINCT descripcion, id, familia_id, numero_departamento FROM familia WHERE familia_id IS NULL GROUP BY descripcion;

I have tried this 

public List getFamiliasPadre() {
        Criteria cri = getSession().createCriteria(Familia.class);
        cri.add(Restrictions.isNull("familia.id")).setProjection(Projections.distinct(Projections.property("descripcion")))
                .setProjection(Projections.groupProperty("descripcion"));
        return cri.list();
    }

But it returns a result like if I were executing this query

SELECT DISTINCT descripcion FROM familia WHERE familia_id IS NULL GROUP BY descripcion;

It only returns column descripcion.

My question is, How can I tell Hibernate that I want all columns of my table but I don't want to repeat vaules from descripcion column?

Solution

  • You just can't do that in plain SQL queries. I mean, how can you possibly merge 2 rows that only share one column? For example, imagine this is your table:

    • familiaId - descripcion - otro
    • 1) null - algo - foo
    • 2) null - algo - bar

    There's no way DISTINCT can apply to just one column and leave the rest aside, because you are expecting to have only one row for those two rows.