Ebean request using setDistinct () does not work

Question

I use ebean request in the game! to find a list of records based on a single column. This seems like a fairly simple request, but the problem is that the ebean setDistinct (true) method does not actually set the request to a separate one.

My request:

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

In my results, I get duplicate artist names.

From what I saw, I think this is the correct syntax, but I could be wrong. I would be grateful for any help. Thanks.

Answer 1

According to issue # 158: add support for using setDistinct (by excluding the id property from the generated sql) in the Ebean error tracker, the problem is that the identifier columns are added to the beginning of the select query implicitly. This makes an excellent keyword in the identifier column, which will always be different.

This is supposed to be fixed in Ebean 4.1.2.

Answer 2

I just ran into the same problem and can't figure it out. As hfs said, it was fixed in a later version, but if you get stuck for a while, you can use

 findSet() 

So in your example use

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

Answer 3

Alternatively, you can use your own SQL query (SqlQuery). The mechanism is described here: https://ebean-orm.imtqy.com/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 4

I have a solution for it: -

RawSql rawSql = RawSqlBuilder .parse ("SELECT different CASE WHEN PARENT_EQUIPMENT_NUMBER 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;