Ebean query using setDistinct() does not work

Question

I'm using an ebean query in the play! framework to find a list of records based on a distinct column. It seems like a pretty simple query but the problem is the ebean method setDistinct(true) isn't actually setting the query to distinct.

My query is:

List<Song> allSongs = Song.find.select("artistName").setDistinct(true).findList();

In my results I get duplicate artist names.

From what I've seen I believe this is the correct syntax but I could be wrong. I'd appreciate any help. Thank you.

Answer 1

I just faced the same issue out of the blue and can not figure it out. As hfs said its been fixed in a later version but if you are stuck for a while you can use

findSet()

So in your example use

List<Song> allSongs = Song.find.select("artistName").setDistinct(true).findSet();

Answer 2

As an alternative you can use a native SQL query (SqlQuery). The mechanism is described here: https://ebean-orm.github.io/apidocs/com/avaje/ebean/SqlQuery.html

This is from the documentation:

public interface SqlQuery
extends Serializable
Query object for performing native SQL queries that return SqlRow's.
Firstly note that you can use your own sql queries with entity beans by using the SqlSelect annotation. This should be your first approach when wanting to use your own SQL queries.

If ORM Mapping is too tight and constraining for your problem then SqlQuery could be a good approach.

The returned SqlRow objects are similar to a LinkedHashMap with some type conversion support added.

// its typically a good idea to use a named query
// and put the sql in the orm.xml instead of in your code

    String sql = "select id, name from customer where name like :name and status_code = :status";

    SqlQuery sqlQuery = Ebean.createSqlQuery(sql);
    sqlQuery.setParameter("name", "Acme%");
    sqlQuery.setParameter("status", "ACTIVE");

    // execute the query returning a List of MapBean objects
    List<SqlRow> list = sqlQuery.findList();

Answer 3

According to issue #158: Add support for using setDistinct (by excluding id property from generated sql) on the Ebean bug tracker, the problem is that an ID column is added to the beginning of the select query implicitly. That makes the distinct keyword act on the ID column, which will always be distinct.

This is supposed to be fixed in Ebean 4.1.2.

Answer 4

i have a solution for it:-

RawSql rawSql = RawSqlBuilder .parse("SELECT distinct CASE WHEN PARENT_EQUIPMENT_NUMBER IS NULL THEN EQUIPMENT_NUMBER ELSE PARENT_EQUIPMENT_NUMBER END AS PARENT_EQUIPMENT_NUMBER " + "FROM TOOLS_DETAILS").create();

    Query<ToolsDetail> query = Ebean.find(ToolsDetail.class);

    ExpressionList<ToolsDetail> expressionList = query.setRawSql(rawSql).where();//ToolsDetail.find.where();

    if (StringUtils.isNotBlank(sortBy)) {
        if (StringUtils.isNotBlank(sortMode) && sortMode.equals("descending")) {
            expressionList.setOrderBy("LPAD("+sortBy+", 20) "+"desc");

            //expressionList.orderBy().asc(sortBy);
        }else if (StringUtils.isNotBlank(sortMode) && sortMode.equals("ascending")) {

            expressionList.setOrderBy("LPAD("+sortBy+", 20) "+"asc");
           // expressionList.orderBy().asc(sortBy);
        } else {
            expressionList.setOrderBy("LPAD("+sortBy+", 20) "+"desc");

        }


    }
    if (StringUtils.isNotBlank(fullTextSearch)) {
        fullTextSearch = fullTextSearch.replaceAll("\\*","%");
        expressionList.disjunction()
                .ilike("customerSerialNumber", fullTextSearch)
                .ilike("organizationalReference", fullTextSearch)
                .ilike("costCentre", fullTextSearch)
                .ilike("inventoryKey", fullTextSearch)
                .ilike("toolType", fullTextSearch);
    }

    //add filters for date range
    String fromContractStartdate = Controller.request().getQueryString("fm_contract_start_date_from");
    String toContractStartdate = Controller.request().getQueryString("fm_contract_start_date_to");
    String fromContractEndtdate = Controller.request().getQueryString("fm_contract_end_date_from");
    String toContractEnddate = Controller.request().getQueryString("fm_contract_end_date_to");

    if(StringUtils.isNotBlank(fromContractStartdate) && StringUtils.isNotBlank(toContractStartdate))
    {

        Date fromSqlStartDate=new Date(AppUtils.convertStringToDate(fromContractStartdate).getTime());
        Date toSqlStartDate=new Date(AppUtils.convertStringToDate(toContractStartdate).getTime());
        expressionList.between("fmContractStartDate",fromSqlStartDate,toSqlStartDate);
    }if(StringUtils.isNotBlank(fromContractEndtdate) && StringUtils.isNotBlank(toContractEnddate))
    {
        Date fromSqlEndDate=new Date(AppUtils.convertStringToDate(fromContractEndtdate).getTime());
        Date toSqlEndDate=new Date(AppUtils.convertStringToDate(toContractEnddate).getTime());
        expressionList.between("fmContractEndDate",fromSqlEndDate,toSqlEndDate);
    }

    PagedList pagedList = ToolsQueryFilter.getFilter().applyFilters(expressionList).findPagedList(pageNo-1, pageSize);

    ToolsListCount toolsListCount = new ToolsListCount();
    toolsListCount.setList(pagedList.getList());
    toolsListCount.setCount(pagedList.getTotalRowCount());
    return toolsListCount;