0

Imagine I have this native SQL query:

SELECT
    # .. other properties
    model.id 'Model Id', mappedCountSubQ.mapcount 'Mapped Count'
FROM
    # .. other join
    model_content_revision modelCts
    JOIN model model ON modelCts.model_id = model.id
    JOIN (
        SELECT 
             modelCts2.model_id 'modelId2', COUNT(modelCts2.id) 'mapcount'
        FROM 
             model_content_revision modelCts2
        GROUP BY 
             modelCts2.model_id
    ) AS mappedCountSubQ ON mappedCountSubQ.modelId2 = model.id

All I want is to extract row count from model_content_revision based on model id, with other property. So I created this query and it works in SQL editor. Now I want to convert it into GORM createCriteria HQL, and I found some obstacles.

I do not know how to perform JOIN in FROM clause, in GORM/HQL. I did googling and found the thing called DetachedCriteria, but all examples only led to use DetachedCriteria in WHERE clause.

Some suggestions in other StackOverflow questions didn't solve my problem, unfortunately.

Thanks for help & regards.

Ichroman Raditya Duwila
  • 101
  • 2
  • 12
  • You can use createAlias() to specify a join and then use projections. example: createAlias('model_content_revision', 'mcr', CriteriaSpecification.LEFT_JOIN) – JMa Nov 02 '18 at 17:00
  • I did use **createAlias()** but it didn't solve my problem somehow since I need to count related model content revision by model id. However, I created a temporary solution by using Hibernate's SQLQuery. Thanks for your response @JMa – Ichroman Raditya Duwila Nov 03 '18 at 06:13

0 Answers0