0

I have created a customer table using service builder like as shown below

 _ _ _ _ _ _ _ _ _ _ _ 
| Id | CusId | CusName |
|_ _ |_ _ _ _| _ _ _ _ |
| 11 |  215  |  John   |
|_ _ |_ _ _ _| _ _ _ _ |
| 12 |  206  |  Lino   |
|_ _ |_ _ _ _| _ _ _ _ | 
| 13 |  215  |  John   |
|_ _ |_ _ _ _| _ _ _ _ |    
| 14 |  215  |  John   |
|_ _ |_ _ _ _| _ _ _ _ |
| 15 |  206  |  Lino   |
|_ _ |_ _ _ _| _ _ _ _ | 
| 16 |  207  |  Nino   |
|_ _ |_ _ _ _| _ _ _ _ |

My service.xml is given below

service.xml

<entity name="Customer" local-service="true" remote-service="false" json-enabled="true">
    <column name="Id" type="int" primary="true"/>
    <column name="CusId" type="int" />
    <column name="CusName" type="String" />
    <finder name="CustomerCount" return-type="Collection">
        <finder-column name="CusId"/>
    </finder>
</entity>

Can anyone please tell me how to generate the below output using finder

 _ _ _ _ _ _ _ _ 
| CusId | Count |
|_ _ _ _|_ _ _ _| 
|  215  |   3   |
|_ _ _ _|_ _ _ _| 
|  206  |   2   | 
|_ _ _ _|_ _ _ _| 
|  207  |   1   |
|_ _ _ _|_ _ _ _|

I am using Liferay 6.2 and database as HSQLDB

Alex Man
  • 4,746
  • 17
  • 93
  • 178

1 Answers1

1

The SQL query to generate the output is:

SELECT "CusId", COUNT("CusId") FROM "CustomerCount" GROUP BY "CusId"

This assumes the table and column names are double-quoted in the database which makes them case-sensitive.

fredt
  • 24,044
  • 3
  • 40
  • 61
  • Thanks for your answer, Apart from this direct query is there any way to achieve it through finder or through mappings – Alex Man Jun 15 '16 at 10:36
  • I don't know about much about Liferay. Try asking via their support channels. – fredt Jun 15 '16 at 12:10