Get count value from a nested sql-query with Liferay Dynamic Query

Question

Could anybody clarify to me if is it possible accomplishing the following sql statement (MVC example) from within the Dynamic Query world:

select count(*) from <sub_select> where aggregate_field >= J;

<sub_select>::
(select case
when condition1 then 1
when condition2 then 2
...
when conditionN then N
else 0
end as aggregate_field
from Entity) as select1

J={1..N}  // J can range 1 to N

The thing is I have to calculate the number of rows satisfiying the where-condition aggregate_field >= J, but the field aggregate_field doesn't belong to the table Entity, but it's calculated depending on some conditions.

There's no problem to create a DynamicQuery object to execute the sub_select:

subQuery = EntityLocalServiceUtil.dynamicQuery();
subQuery.setProjection(ProjectionFactoryUtil.sqlProjection("case when ... end as aggregate_field", new String[] { "aggregate_field" }, new Type[] { Type.INTEGER });
EntityLocalServiceUtil.dynamicQuery(subQuery);

But I don't know how to do to create a DynamiQuery object to get the number of rows of the table linked to subQuery satisfiying the where-condition.

Any ideas about how to accomplish the intended task will be appreciate.

Answer 1

In that case it's better to write a custom sql query. DynamicQuery maybe it's not the right way.

Look at https://help.liferay.com/hc/en-us/articles/360018179071-Developing-Custom-SQL-Queries

Answer 2

I'd like to share how I've managed to get a raw database connection. My solution is based on the use of the class DLFolderUtil, which is a persistence utility that wraps the -Impl class com.liferay.portlet.documentlibrary.service.persistence.impl.DLFolderPersistenceImpl, which provides direct access to the database for CRUD operations. Then, the way to get acess to the openSession() method is as described next:

Session session = DLFolderUtil.getPersistence().openSession();
                    

And then, you can work the way you do with an Hibernate session:

String sql = "select ... "; // whatever complex query you can think of
SQLQuery q = session.createSynchronizedSQLQuery(sql);
List<?> list = q.list();

I don't know if what I've done is a good practice or not to do when working with the original Liferay database, but it's a kind of a quite easy solution.