CODEWITHSUNDEEP
grailsgrails-orm
Hamza Zaib
Hamza Zaib

Reputation: 23

Get count of child objects in grails Criteria query

Lets Say a domain class A has many Class B objects. I need to do a criteria query which returns

  1. A.id
  2. A.name
  3. B.count(no of B elements associated with A)
  4. B.last Updated(date of most recent update of B elements associated with A considering i have last_updated date for all B elements)

Also the query should be flexible enough to add conditions/restrictions to both A and B domain objects. Currently I have gotten as far as this:

A.createCriteria().list {
    createAlias('b','b')
    projections{
        property('id')
        property('gender')
        property('dateOfBirth')
        count('b.id')
        property('publicId')
    }
}

But the problem is that it only returns one object and the count of child objects is for all the elements of B instead of just those associated with A

Upvotes: 1

Views: 1685

Answers (3)

Nic Olas
Nic Olas

Reputation: 471

In the criteria you need to group by the property which are not aggregate.
Try following: 

A.createCriteria().list {
    createAlias('b','b')
    projections{
        groupProperty('id','id')
        groupProperty('gender','gender')
        groupProperty('dateOfBirth','dateOfBirth')
        count('b.id','total')
        groupProperty('publicId','publicId')
    }
}

or If you want to have a list of map object return you can try add resultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP) 

 A.createCriteria().list {
    resultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP)
    createAlias('b','b')
    projections{
        groupProperty('id','id')
        groupProperty('gender','gender')
        groupProperty('dateOfBirth','dateOfBirth')
        count('b.id','total')
        groupProperty('publicId','publicId')        
    }
}

Hope it can help

Upvotes: 0

Hamza Zaib
Hamza Zaib

Reputation: 23

Turns out I wasn't very far from the solution and i just needed to do grouping based on the right property which is the foreign key column in the child table which is b.a in this case so the following works now

A.createCriteria().list {
  createAlias('b','b')
  projections{
    property('id')
    property('gender')
    property('dateOfBirth')
    count('b.id')
    groupProperty('b.a')
    property('publicId')
    }
}

Upvotes: 1

Mario
Mario

Reputation: 4998

Recently I was in a similar scenario I needed a query in which one of your rows will store the count of many in a one-to-many relationship

But unlike your scenario I used native sql queries to resolve the query.

The solution was to use derived tables (I do not know how to implement them using criteria query).

In case you find it useful I share a code with the implementation taken from a grails service:

List<Map> 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
    }

The part of interest is to declare a row within the main select and then in the clause from declare the derived query that stores the result in a row with the same name declared in the main select

SELECT ...
       total_tasks --Add the count column to your select
FROM ticket t
JOIN (SELECT ticked_id, COUNT(1) as total_tasks
      FROM tasks
      GROUP BY ticked_id) ta ON t.id = ta.ticked_id
...rest of query

This last example I share from the answer made by the user Aaron Dietz to the question that I also formulate

I hope it is useful for you

Upvotes: 1

Related Questions