Use DetachedCriteria in JOIN Clause in Gorm/Grails

Question

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.