transforming a SQL query to dynamic query with joins or subquery

Question

I've a SQL query (2 variants) and I need to use it with dynamic queries.

In SQL it looks so (Variant 1 // via subquery):

SELECT AssetEntry.entryId , (
              SELECT COUNT(*)
              FROM `MBMessage`
              WHERE classPK = AssetEntry.classPK
            ) AS comments
            FROM `AssetEntry`
            ORDER BY comments DESC

Or the alternative query with join and group:

SELECT AssetEntry.entryId, count(MBMessage.classPK)
 FROM `AssetEntry`
 JOIN MBMessage ON (AssetEntry.classPK = MBMessage.classPK)
 GROUP BY MBMessage.classPK

Both SQL Queries displays exactly the same!

Now I need to use one of them as dynamic query. I have no idea how to do a join and I have no idea how to do a subquery in the projection?!

Can anybody help me? THX

---

I had to do this with custom-sql.

Answer 1

You have another way of doing this, With AssetEntryQuery.

AssetEntryQuery aeq = new AssetEntryQuery();
aeq.setClassName(MBMessage.class.getName());
aeq.set.... (Add any other criterions if you want to)
List<AssetEntry> assetEntries = AssetEntryServiceUtil.getEntries(aeq);

Answer 2

Your requirement is a really specific case, which requires an aggregate-function in the SELECT statement.

I would suggest using Custom query (also known as custom-sql in liferay) with finders in your case, instead of a DynamicQuery.

DynamicQuery API has limitations and will not work in your case (speaking from experience, so if somebody else has other opinions or facts regarding the below points I would be more than happy to know):

1. Joins are not possible with DynamicQuery.
2. Its possible to return a count or to return an individual columns value using Projection but not possible to return a column and count to-gather using projections.
3. Can use sub-query with DynamicQuery using projections but I don't think you can use a sub-query in the select statement with DynamicQuery.