grails - 通过使用条件查询从与组的关系中获取特定列

Question

我在制定标准查询时遇到问题。

我正在查看为特定用户获得不同的Season(id，name)，League(id，name)，以便可以将其转换为缩进列表:

SeasonA

LeagueA

LeagueB

...

SeasonB

LeagueA

LeagueC

...

域类:

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
}

条件查询:

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')
        }
    }
}   

产生的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

我可以看到它缺少季节属性，也没有按季节分组。

我注意到最后一个关闭似乎是它将选择的内容。如果我切换季节和联赛关闭，我将获得季节字段。

我决定尝试别名:

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')
        }
    }   
}

但是，我得到了 QueryException: duplicate association path: season。同样，第一个关闭将是将引发错误的关闭。

由于我什至没有得到重复，所以我还没有解决查询的不同部分。

更新＃2

最终条件查询，并添加分组依据:

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] }
            ]
        }

Answer 1

问题是您的投影层次结构向后。您需要从projections开始，然后进入关联:

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()
        } 
    }
} 

您没有使用聚合函数，因此不需要 groupProperty()。

归一化结果

上面的查询返回一个平面列表，这使创建缩进列表成为一个挑战。您必须进行迭代并跟踪 session 的更改时间。我们可以做得更好。首先，将结果从 List<List>更改为 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()
        } 
    }
} 

在这种情况下， map 结果转换器与属性别名一起使查询返回如下内容:

[
    [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']
]

到目前为止，结果的唯一变化是您现在拥有可用于引用数据的键。在大脑上，这比数组索引容易得多。数据仍然是平坦的(非正规化的)，但是很容易处理:

def seasons = seasonList
    .groupBy { it.seasonId }
    .collect { seasonId, records -> 
        [
            id: seasonId, 
            name: records.head().seasonName, 
            leagues: records.collect { [id: it.leagueId, name: it.leagueName] }
        ]
    }

结果如下:

[
    [
        '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']
        ]
    ]
]

现在，您可以轻松地使用嵌套数据来创建缩进列表。这是一个简单的Groovy示例，可以很容易地适应GSP:

seasons.each { season ->
    println "$season.name"
    season.leagues.each { league -> println "\t$league.name" }
}

上面的示例显示以下内容:

Season A
    League A
    League B
Season B
    League A
    League C

排除标准化

通过分解为方法并传入数据和配置，可以使功能更可重用:

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]] }
        ]
    }
}

然后，您可以使用查询输出和Map来调用该方法，以告诉该方法要使用哪些属性。

def seasons = normalize(seasonList, [id: 'seasonId', name: 'seasonName', children: [key: 'leagues', id: 'leagueId', name: 'leagueName']])