How to use the group_concat function and other arbitrary SQL in Moqui?

Technical Questions
1

Hello All,

I need to concat values for certain records that have common IDs but different values for an entity.
I want to combine the values for the grouped records by fetching them separated by comma.

Ex.

Entity records

ID Value
1 a
1 b
2 a

View output

ID Value
1 a,b
2 a

I could do that in raw sql using the Group_concat function but could not find this function while creating views in Moqui. Following is the SQL query.

select ID, group_concat(Value) from B group by ID;

In Moqui, I could find functions like concat and concat_ws. But these functions are meant to concat multiple columns into one but it can not group multiple rows.

Is there any way to achieve this SQL output through Moqui View.

Any help would be appreciated!

1 Like
2

Welcome!

It’s helpful to know a higher level reason for something like this, because that problem may already be solved like with a view entity or a data document.

I would do this by doing a find on the entity by id, if you have it, or join by an entity id in a groovy script.

3

The Entity Facade does not come close to supporting everything that can be done with SQL in every database. For this sort of query where the results don’t fit very well into the simple entity concept, except maybe with a more complex view-entity alias.

For custom SQL in Moqui there are 4 options that come to mind (there may be more, and you’d think I’d know all of them, but there’s a lot in Moqui, and sometimes you have to research a particular need to reliably find options):

  1. for the WHERE clause use the WhereCondition condition class, which allows you to specify arbitrary SQL that is combined with other conditions
  2. for the SELECT clause use a view-entity and an alias element with complex-alias sub-element(s); for examples of some more complex SQL see ProductAssetViewEntities.xml and other view entity files with “complex-alias”
  3. use ec.entity.sqlFind() for arbitrary SQL that gets executed and returns an EntityListIterator; this requires a view-entity so it knows the fields (aliases) and their types and such, but the view-entity will not be used to generate SQL since you are specifying it manually
  4. use JDBC directly with a Connection object from ec.entity.getConnection()

In your case #2 might be adequate, and if not use #3 or #4.

4

Thank you for your responses @jonesde and @michael .

1 Like