CODEWITHSUNDEEP
java
None
None

Reputation: 9247

How to group by and concate values?

I have this method: 

 public List<IncomeChannelCategoryMap> allIncomeChannels(final List<String> list) {

        final CriteriaQuery<IncomeChannelCategoryMap> criteriaQuery = builder.createQuery(IncomeChannelCategoryMap.class);
        final Root<IncomeChannelMapEntity> root = criteriaQuery.from(IncomeChannelMapEntity.class);

        final List<Selection<?>> selections = new ArrayList<>();
        selections.add(root.get(IncomeChannelMapEntity_.incomeChannel).get(IncomeChannelEntity_.code));
        selections.add(root.get(IncomeChannelMapEntity_.logicalUnitCode));
        selections.add(root.get(IncomeChannelMapEntity_.logicalUnitIdent));
        selections.add(root.get(IncomeChannelMapEntity_.keyword));
        criteriaQuery.multiselect(selections);
        Predicate codePredicate = root.get(IncomeChannelMapEntity_.incomeChannel).get(IncomeChannelEntity_.code).in(list);
        criteriaQuery.where(codePredicate);
        return entityManager.createQuery(criteriaQuery).getResultList();

    }

And response is like this :

[
{
    "incomeChannelCode": "DIRECT_SALES",
    "logicalUnitCode": "R_CATEGORY",
    "logicalUnitIdent": "7"
  },
  {
    "incomeChannelCode": "DIRECT_SALES",
    "logicalUnitCode": "R_CATEGORY",
    "logicalUnitIdent": "8"
  }
]

What im trying achive is this :

  {
    "incomeChannelCode": "DIRECT_SALES",
    "logicalUnitCode": "R_CATEGORY",
    "logicalUnitIdent": "7,8"
  }

Any suggestion how can i achive this?

I tried this , that i found on some example :

builder.function("group_concat", String.class, root.get(IncomeChannelMapEntity_.logicalUnitIdent));

But this is not working. Any other suggestion?

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class IncomeChannelCategoryMap implements Serializable {
    private static final long serialVersionUID = 1L;

    private String incomeChannelCode;
    private String logicalUnitCode;
    private String logicalUnitIdent;
    private String keyword;

}

Upvotes: 0

Views: 99

Answers (2)

Mustahsan
Mustahsan

Reputation: 3862

Try this:

ArrayList<IncomeChannelCategoryMap> list = entityManager.createQuery(criteriaQuery).getResultList();

List<IncomeChannelCategoryMap> finalList = new ArrayList<>(list.stream().collect(
                 Collectors.toMap(IncomeChannelCategoryMap::getIncomeChannelCode, Function.identity(), (IncomeChannelCategoryMap i1, IncomeChannelCategoryMap i2) -> {
                     i1.setLogicalUnitIdent(i1.getLogicalUnitIdent()+","+i2.getLogicalUnitIdent());
                     return i1;
                 })).values());

 return finalList;

NOTE: Please add your getter methods accordingly, I just assumed you have these method names.

Upvotes: 1

Mushif Ali Nawaz
Mushif Ali Nawaz

Reputation: 3866

You should use the Native Query approach as I suggested in your previous question: 

public List<IncomeChannelCategoryMap> allIncomeChannels(final List<String> list) {
    List<Object[]> resultList = entityManager.createNativeQuery(
            "select income_channel_code, logicalunit_code, string_agg(logicalunitident,',') idents, keyword from r_income_channel_map where income_channel_code in (:codes) group by logicalunit_code, income_channel_code, keyword")
            .setParameter("codes", list).getResultList();
    return resultList.stream().map(IncomeChannelCategoryMap::new).collect(Collectors.toList());
}

You need to add this constructor to your IncomeChannelCategoryMap class: 

IncomeChannelCategoryMap(Object[] objects) {
    this.incomeChannelCode = (String) objects[0];
    this.logicalUnitCode = (String) objects[1];
    this.logicalUnitIdent = (String) objects[2];
    this.keyword = (String) objects[3];
}

Upvotes: 0

Related Questions