Appetizer: [I tend to explain the Use-case of the topic before jumping on to
it. You can skip this Appetizer section (as I like to call it) if you are already aware of Liferay's
Expando API -or- if you are too hungry to start the main course :-)]
There are number of commonly used attributes provided by Liferay for entities like Users, Blogs, Web Contents, etc. Even then, we come across situations where we need additional attribute(s). Liferay provides a very useful feature called "Custom Fields". You can add additional attributes to these entities using Control Panel > Portal > Custom Fields as shown in the below screenshot:
In most of the projects where Liferay integrates with external systems, we need to use Custom Fields to relate the identity of Users in external systems with the Liferay User. I too have used Custom Fields when integrated Liferay with Plateau, Moodle, and SAP.
Custom Fields are also known as 'Expando' attributes (cool name, huh?). You can create Custom Fields for almost all the entities like User, Page, Organization, Blog, Message Board, etc.
In most of our applications we also need to provide "Reports" [for example: Number of active users, Number of users who logged-in in month x, Number of forum discussions, Users participating in different forums, etc]. You may use Jasper Reports or BIRT or any other reporting engine of your choice for your Reporting requirements.
Reports understand SQL. They talk to the Database via the Reporting engine like Jasper Report, BIRT, etc. So, knowledge of Liferay's Expando tables is essential for writing SQL that involves custom fields.
Liferay stores Custom Fields in below 4 tables known as 'Expando' tables:
Main Course:
I have added 2 custom fields namely 'ProjectId' and 'LmsUserId' to User object. I want to print all the attributes of a user - including ProjectId and LMSUserId:
MySQL provides a very useful function called GROUP_CONCAT. Like PIVOT Tables of Excel, it concats all the values of a column into single value (separated by comma). We will combine this with IF() function to separate out values as shown below:
The output of this query will look like this:
Hope this helps!
Do provide your feedback if you think I can improve this post. You can also subscribe to my blog by providing your email id in 'Follow by Email' section in the Right column.
There are number of commonly used attributes provided by Liferay for entities like Users, Blogs, Web Contents, etc. Even then, we come across situations where we need additional attribute(s). Liferay provides a very useful feature called "Custom Fields". You can add additional attributes to these entities using Control Panel > Portal > Custom Fields as shown in the below screenshot:
In most of the projects where Liferay integrates with external systems, we need to use Custom Fields to relate the identity of Users in external systems with the Liferay User. I too have used Custom Fields when integrated Liferay with Plateau, Moodle, and SAP.
Custom Fields are also known as 'Expando' attributes (cool name, huh?). You can create Custom Fields for almost all the entities like User, Page, Organization, Blog, Message Board, etc.
It may interest you to know that Liferay's 'Web Forms' portlet also uses Expando APIs to store definition of Web Forms as well as the form data.
In most of our applications we also need to provide "Reports" [for example: Number of active users, Number of users who logged-in in month x, Number of forum discussions, Users participating in different forums, etc]. You may use Jasper Reports or BIRT or any other reporting engine of your choice for your Reporting requirements.
Reports understand SQL. They talk to the Database via the Reporting engine like Jasper Report, BIRT, etc. So, knowledge of Liferay's Expando tables is essential for writing SQL that involves custom fields.
Liferay stores Custom Fields in below 4 tables known as 'Expando' tables:
- ExpandoTable - Definition of the custom fields table
- ExpandoRow - Maps the row/record of the Liferay entity - to custom field record
- ExpandoColumn - Stores the names of the custom fields
- ExpandoValue - Stores actual data of the custom field
Main Course:
I have added 2 custom fields namely 'ProjectId' and 'LmsUserId' to User object. I want to print all the attributes of a user - including ProjectId and LMSUserId:
MySQL provides a very useful function called GROUP_CONCAT. Like PIVOT Tables of Excel, it concats all the values of a column into single value (separated by comma). We will combine this with IF() function to separate out values as shown below:
SELECT u.userId, u.emailAddress, u.firstName, u.lastName,
GROUP_CONCAT(IF(ec.name='projectId', ev.data_, NULL)) AS `ProjectId`,
GROUP_CONCAT(IF(ec.name='lmsUserId', ev.data_, NULL)) AS `LmsUserId`FROM User_ u JOIN ExpandoValue ev ON u.userId = ev.classPKJOIN ExpandoColumn ec ON ec.columnId = ev.columnId GROUP BY u.userId;
The output of this query will look like this:
Hope this helps!
Do provide your feedback if you think I can improve this post. You can also subscribe to my blog by providing your email id in 'Follow by Email' section in the Right column.
References:
1 comment:
Bravo! This worked like a charm.
Post a Comment