Translating SQL query to Grails

Question

How do I translate the following 3 table SQL query to Grails?

SELECT t.name, count(*) as c
FROM topic t
LEFT OUTER JOIN article_topics at
ON t.id = at.topic_id
LEFT OUTER JOIN article a
ON at.article_id = a.id
GROUP BY t.name

I've been trying something similar to but not sure how to do the joins

def criteria = Topic.createCriteria()
    criteria.list {
        groupProperty("name")
        projections {
            count('*')
        }
    }

Or maybe it's better/easier if I attempt to use

Topic.executeQuery(..insert sql..)

In case it helps, I have Article and Topic GORM objects and in Article

static mapping = {
  topics lazy: false, joinTable: [name: 'article_topics', column: 'topic_id', key: 'article_id']
}

Answer 1

Todd. In grails you can use hibernate native sql queries as described in this post by mr hacki.

I share an example taken from code in production that uses the technique described in the publication and uses left join.

List<Map<String, Object>> resumeInMonth(final String monthName) {
    final session = sessionFactory.currentSession
    final String query = """
        SELECT
            t.id AS id,
            e.full_name AS fullName,
            t.subject AS issue,
            CASE t.status
                WHEN 'open' THEN 'open'
                WHEN 'pending' THEN 'In progress'
                WHEN 'closed' THEN 'closed'
            END AS status,
            CASE t.scheduled
                WHEN TRUE THEN 'scheduled'
                WHEN FALSE THEN 'non-scheduled'
            END AS scheduled,
            ifnull(d.name, '') AS device,
            DATE(t.date_created) AS dateCreated,
            DATE(t.last_updated) AS lastUpdated,
            IFNULL(total_tasks, 0) AS tasks
        FROM
            tickets t
                INNER JOIN
            employees e ON t.employee_id = e.id
                LEFT JOIN
            devices d ON d.id = t.device_id
                LEFT JOIN
            (SELECT
                ticket_id, COUNT(1) AS total_tasks
            FROM
                tasks
            GROUP BY ticket_id) ta ON t.id = ta.ticket_id
        WHERE
            MONTHNAME(t.date_created) = :monthName
        ORDER BY dateCreated DESC
    """
    final sqlQuery = session.createSQLQuery(query)
    final results = sqlQuery.with {
        resultTransformer = AliasToEntityMapResultTransformer.INSTANCE

        setString('monthName', monthName)

        list()
    }

    results
}

I hope it is useful for you