I'm having issues formulating a criteria query.
I'm looking at getting distinct Season(id, name), League(id, name) for a specific user, so that I can turn it into a indented list:
Domain classes:
class League {
String name
User user
static hasMany = [games:Game]
}
class Season {
String name
User user
static hasMany = [games:Game]
}
class Game {
Season season
League league
}
class User {
String username
String password
String email
}
Criteria query:
def id = 1
def seasonList = Game.createCriteria().list{
season{
projections{
property('id')
property('name')
groupProperty('id')
}
user{
eq 'id', id.toLong()
}
}
league{
projections{
property('id')
property('name')
groupProperty('id')
}
}
}
Resulting SQL:
select
league_ali3_.id as y0_,
league_ali3_.name as y1_,
league_ali3_.id as y2_
from
game this_
inner join
league league_ali3_
on this_.league_id=league_ali3_.id
inner join
season season_ali1_
on this_.season_id=season_ali1_.id
inner join
user user_alias2_
on season_ali1_.user_id=user_alias2_.id
where
(
(
user_alias2_.id=?
)
)
group by
league_ali3_.id
I can see that it's missing the season properties and it's not grouping by season either.
I noticed that the last closure seems to be what it will select. If I switch the season and league closures, I get the season fields instead.
I decided to try aliases:
def seasonList = Game.createCriteria().list{
createAlias('season','s')
createAlias('league','l')
season{
projections{
property('s.id')
property('s.name')
groupProperty('s.id')
}
user{
eq 'id', id.toLong()
}
}
league{
projections{
property('l.id')
property('l.name')
groupProperty('l.id')
}
}
}
However, I get a QueryException: duplicate association path: season. Again, the first closure will be the one that will raise the error.
As I wasn't even getting duplicates, I have yet to tackle the distinct part of my query.
Update #2
Final criteria query, with added group by:
def seasonList = Game.createCriteria().list{
resultTransformer = new org.hibernate.transform.AliasToEntityMapResultTransformer()
projections {
season {
property('id', 'seasonId')
property('name', 'seasonName')
groupProperty('id')
}
league {
property('id', 'leagueId')
property('name', 'leagueName')
groupProperty('id')
}
}
season {
user {
eq 'id', id.toLong()
}
}
}
def seasons = seasonList
.groupBy { it.seasonId }
.collect { seasonId, records ->
[
id: seasonId,
name: records.head().seasonName,
leagues: records.collect { [id: it.leagueId, name: it.leagueName] }
]
}
A problem is that you've got the projections hierarchy backwards. You need to start at projections and then go down into the associations:
def id = 1
def seasonList = Game.createCriteria().list{
projections {
season {
property('id')
property('name')
}
league {
property('id')
property('name')
}
}
season {
user {
eq 'id', id.toLong()
}
}
}
You're not using aggregate functions, so you shouldn't need groupProperty().
The above query returns a flat list, making it a challenge to create an indented list; you'd have to iterate and keep track of when the session changes. We can do better. Let's start by changing the result from a List<List> to a List<Map>:
def id = 1
def seasonList = Game.createCriteria().list{
resultTransformer = new org.hibernate.transform.AliasToEntityMapResultTransformer()
projections {
season {
property('id', 'seasonId')
property('name', 'seasonName')
}
league {
property('id', 'leagueId')
property('name', 'leagueName')
}
}
season {
user {
eq 'id', id.toLong()
}
}
}
The map result transformer, in this case along with property aliases, makes the query return something like this:
[
[seasonId: 1, seasonName: 'Season A', leagueId: 100, leagueName: 'League A'],
[seasonId: 1, seasonName: 'Season A', leagueId: 200, leagueName: 'League B'],
[seasonId: 2, seasonName: 'Season B', leagueId: 100, leagueName: 'League A'],
[seasonId: 2, seasonName: 'Season B', leagueId: 300, leagueName: 'League C']
]
So far, the only change in the result is that you now have keys you can use to reference the data. This is much easier on the brain than array indices. The data is still flat (denormalized), but that's easy to take care of:
def seasons = seasonList
.groupBy { it.seasonId }
.collect { seasonId, records ->
[
id: seasonId,
name: records.head().seasonName,
leagues: records.collect { [id: it.leagueId, name: it.leagueName] }
]
}
This is the outcome:
[
[
'id':1, 'name':'Season A', 'leagues':[
['id':100, 'name':'League A'],
['id':200, 'name':'League B']
]
],
[
'id':2, 'name':'Season B', 'leagues':[
['id':100, 'name':'League A'],
['id':300, 'name':'League C']
]
]
]
Now, you can easily work with the nested data to create an indented list. Here's an example in plain Groovy which can easily be adapted to GSP:
seasons.each { season ->
println "$season.name"
season.leagues.each { league -> println "\t$league.name" }
}
The example above prints the following:
Season A
League A
League B
Season B
League A
League C
The functionality can be made more reusable by factoring out into a method and passing in the data and configuration:
def normalize(List rows, Map config) {
rows.groupBy { it[config.id] }
.collect { id, records ->
[
id: id,
name: records.head()[config.name],
(config.children.key): records.collect { [id: it[config.children.id], name: it[config.children.name]] }
]
}
}
Then, you can call the method with the query output and a Map telling the method which properties to use.
def seasons = normalize(seasonList, [id: 'seasonId', name: 'seasonName', children: [key: 'leagues', id: 'leagueId', name: 'leagueName']])