0

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.

Tim Post
  • 33,371
  • 15
  • 110
  • 174
appsthatmatter
  • 6,347
  • 3
  • 36
  • 40

2 Answers2

0

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.
Nightfirecat
  • 11,432
  • 6
  • 35
  • 51
Prakash K
  • 11,669
  • 6
  • 51
  • 109
0

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);
Nightfirecat
  • 11,432
  • 6
  • 35
  • 51
Sharana
  • 749
  • 3
  • 8