11/21/2010

How to do a Subquery in Liferay using DynamicQuery (1)

So ... first time in english ... need more visitors ;)

Problem: Loading all Users together with their GroupRoles is not that easy. 

Using
  • UserLocalServieUtil.getUserById and 
  • UserGroupRoleLocalServiceUtil.hasUserGroupRole
is way to slow. We had the case where we were inspecting 2000 Users and it took more than 15 seconds to do that.

Solution: Use dynamic queries !!

DynamicQueries are the preferred way to specific Statements in Liferay that are translated directly into vendor - specific SQL, executed super-fast and return only what you need. No hidden lazy - loading afterwards, no need to verify roles one after another.

The good thing about the DynamicQueries is that they can be specified outside ext environment which makes it possible to define them in the portlet directly and to modify them without having to restart the Server (Liferay 5.x).

So here is a simple example:



DynamicQuery userGroupRoleQuery = DynamicQueryFactoryUtil.forClass(UserGroupRole.class, "userGroupRole",
PortalClassLoaderUtil.getClassLoader());
userGroupRoleQuery.setProjection(
ProjectionFactoryUtil.property("primaryKey.userId"));
UserGroupRoleLocalServiceUtil.dynamicQuery(userGroupRoleQuery );

So ... what do we do here ? First we specify to access the table for the Entity that referrs to the class UserGroupRole. We use the PortalClassLoader to gain access to the UserGroupRoleImpl class, otherwise a NullPointer will be the result.

Next - we use a Projection to change the return type from UserGroupRole objects to just the userId (of type long). We need to specify "primaryKey" because Liferay internally maps the userId, the groupId and the roleId to the primary key. This can be verified by taking a look at the hibernate configuration file at portal-hbm.xml in the portal sourcen under portal-impl/META-INF.

This query can now be executed and returns all userIds.

To connect this DynamicQuery with another DynamicQuery retrieving all Users we first remove the line where we execute the DynamicQuery and add the following :


DynamicQuery userQuery = DynamicQueryFactoryUtil.forClass(User.class,"user", PortalClassLoaderUtil.getClassLoader());
userQuery.add(PropertyFactoryUtil.forName("userId").in( userGroupRoleQuery));


This sends both Queries, the UserGroupRole query as subquery to the Database and lets the database handle the retrieval and optimization. For cases where databases only accept a limited number of entries in an "in" clause (Oracle accepts only 1000), this is also an optimization because it doesn´t matter how much results the the userGroupRoleQuery has - the database optimizes it.

In our case we gained a performance boost of about 1000% (10 times faster).

This was part 1 ... in part 2 we will handle several specialities gaining access to spacial database entries.


If you like this tutorial it would be very nice, if you could click on some of the google ads you see on the right side. It helps me run this block and motivates me ;)

If you have any questions, feel free to leave a comment.