Introduction

So I get finished which the previous blog, Visiting DynamicQuery, and I shared it with my team. I'm always kind of happy when I get a new blog post done, so I share it with everyone I can think of.

The first comment I got was from my friend and teammate, Shane Merriss:

Hey Dave, thanks for the blog post, I’ll be able to share it with my client since we use it in a few places. Do you have a solution for adding the HAVING clause in a statement?

I realized that I really did only cover the basics and had not touched on more advanced DQ scenarios.

So Shane, this one's for you. Well, you and anyone else who can get some value out of it :D

Advanced Dynamic Queries

Using Restrictions, Projections, Properties and Orders, we can build out some decent although simple SQL statements.

SELECT <Projections> or <Entity> FROM <Entity> WHERE <Restrictions> ORDER BY <Orders> can cover many query use cases.

But sometimes we need to go beyond the simple cases, so I'm going to share some examples...

Grouping

The Projections do offer the ability to handle some grouping for us automatically.

Say we wanted to know how many MBThreads were assigned to each category. We can accomplish this using the following:

DynamicQuery dq = mbThreadLocalService.dynamicQuery();

dq.setProjection(ProjectionFactoryUtil.projectionList()
  .add(PropertyFactoryUtil.forName("categoryId").group())
  .add(PropertyFactoryUtil.forName("threadId").count()));

List<Long[]> categoryThreadCounts = mbThreadLocalService.dynamicQuery(dq);

So this code roughly translates into SELECT categoryId, count(threadId) FROM MBThread GROUP BY categoryId.

The PropertyFactoryUtil creates a Projection for a named field in the object, but from there we apply one of the available aggregation methods (i.e. count(), sum() avg(), etc.).

We get a return with two Long objects, the first being the categoryId, the second the count of threads in that category. Easy peasy.

Maybe we will want to sort this in descending order so we can find those categories that have the most threads. We'll need to alter our code a little bit to add an alias, then we can add an order on the alias.

DynamicQuery dq = mbThreadLocalService.dynamicQuery();

dq.setProjection(ProjectionFactoryUtil.projectionList()
  .add(PropertyFactoryUtil.forName("categoryId").group())
  .add(PropertyFactoryUtil.forName("threadId").count(), "threadCount"));

dq.addOrder(OrderFactoryUtil.desc("threadCount"));

List<Long[]> categoryThreadCounts = mbThreadLocalService.dynamicQuery(dq);

With these two changes, we effectively have the SQL SELECT category, count(threadId) as threadCount FROM MBThread GROUP BY categoryId ORDER BY threadCount DESC.

SQL Projections

Sometimes you just really want to be able to use some native function that is provided by the database but not possible to do using the regular DynamicQuery projections. When a case like this comes up, SQL Projections will come to your rescue.

Let's assume that our Liferay database is hosted on the Postgres database. Postgres has this function, RANK(), that can be used to assign a ranking to each row in a result set. Bringing forward our previous example of categories with thread counts, we might want to leverage the Postgres RANK() function to give a nice ranking to the rows.

We'll be able to handle this using a SQL Projection. We'll further modify our example to include the SQL projection:

DynamicQuery dq = mbThreadLocalService.dynamicQuery();

dq.setProjection(ProjectionFactoryUtil.projectionList()
  .add(PropertyFactoryUtil.forName("categoryId").group())
  .add(PropertyFactoryUtil.forName("threadId").count(), "threadCount")
  .add(ProjectionFactoryUtil.sqlProjection("RANK () OVER (ORDER BY threadCount DESC) AS ranking", 
    new String[] { "ranking" }, new Type[] { Type.LONG }));

dq.addOrder(OrderFactoryUtil.asc("ranking"));
dq.addOrder(OrderFactoryUtil.asc("categoryId"));

List<Long[]> categoryThreadCounts = mbThreadLocalService.dynamicQuery(dq);

We have now added a third column into our result set named "ranking". It will be another Long value, and it will be the result of the RANK() function. The category with the highest thread count will have the ranking of 1, the next will have the ranking 2, if the next two categories have the same thread count they will each have a tied ranking of 3, ...

Notice how the last two arguments of the sqlProjection() call are arrays? You can actually add multiple columns using the SQL fragment, comma separated. For the number of columns you add, though, be sure to populate the arrays accordingly.

Note that the sqlProjection() usage is not limited to Postgres, you can use it for any database you want. That said, not all functions are going to be supported on all databases. In this example, the RANK() function is (I believe) exclusive to Postgres, so while sqlProjection() works with any database you want, RANK() certainly won't. 

SQL Group Projections

The SQL Group Projection is similar to the simpler SQL Projection, but this method includes the ability to have a GROUP BY as part of the query.

It also happens to be the only way (that I'm aware of) to have a full GROUP BY / HAVING clause when using Dynamic Queries.

So going back to our MBThread example, let's say that we're only interested in retrieving the category ids that have at least 1,000 threads; anything less than that, we don't really care about.

We'll solve this guy using an SQL Group Projection as follows:

DynamicQuery dq = mbThreadLocalService.dynamicQuery();

dq.setProjection(ProjectionFactoryUtil.projectionList()
  .add(PropertyFactoryUtil.forName("categoryId"))
  .add(ProjectionFactoryUtil.sqlGroupProjection("count(threadId) as threadCount", 
    "categoryId HAVING count(threadId)>999",
    new String[] { "threadCount" }, new Type[] { Type.LONG }));

List<Long[]> categoryThreadCounts = mbThreadLocalService.dynamicQuery(dq);

The syntax has changed a little, but the effect is still partially the same. We're still getting the count of threads, grouped by the categoryId, but we've thrown in a HAVING clause to exclude results with a lower thread count.

A couple of things to point out here... I was disappointed that I could not use "categoryId HAVING threadCount>999". When I tried it I basically got an exception because threadCount wasn't defined, so it just couldn't handle the new column alias within the query.

Additionally I tried to add a sort for the threadCount column but got a similar error, unable to find the threadCount field of MBThread. In order to be able to sort on the threadCount column, I had to wrap the sqlGroupProjection in a separate alias declaration:

DynamicQuery dq = mbThreadLocalService.dynamicQuery();

dq.setProjection(ProjectionFactoryUtil.projectionList()
  .add(PropertyFactoryUtil.forName("categoryId"))
  .add(ProjectionFactoryUtil.alias(
    ProjectionFactoryUtil.sqlGroupProjection("count(threadId) as threadCount", 
      "categoryId HAVING count(threadId)>999",
      new String[] { "threadCount" }, new Type[] { Type.LONG }), "threadCount")));

dq.addOrder(OrderFactoryUtil.desc("threadCount"));

List<Long[]> categoryThreadCounts = mbThreadLocalService.dynamicQuery(dq);

The generated SQL for this is: SELECT categoryId, count(threadId) AS threadCount FROM MBThread GROUP BY categoryId HAVING count(threadId)>999 ORDER BY threadCount DESC, exactly the kind of thing we were looking for.

The sort thing is disappointing. The alias() call can only apply a single alias, so there can't be multiple columns defined in the sqlGroupProjection()'s SQL fragment, only a singleton. So I guess you have the choice between being able to sort on the column vs having multiple columns.

So we could have the sort, or we could add an additional column for RANK(), but we can't do both unfortunately.

Note that this issue and the HAVING alias issue above, well those are not really Liferay problems, they come from Hibernate itself. So Liferay can't really fix them as they are Hibernate issues.

Conclusion

Well, we have here some more advanced dynamic query usage, so we have taken a good tool for your toolbox and made it even better.

Hopefully with these details you'll be able to handle your own advanced DQ usage without having to resort completely to custom SQL.