QueryBuilder in 6.2.x #5490
Description
Describe the bug
I have simple game database with Player, GameSession (@ManyToOne) and GameAction (@ManyToOne) entities.
I'd like to run simple query to get aggregate max and avg values for game action for giver user.
Since mikro-orm 6.2.x query builder creates different query than before, which fails.
Strange thing is that when I run query.getQuery() before execute, it produces same query as in 6.1.x:
Update: Apparently it's because it finalizes the query and so it's not modified in the execute method
Reproduction
Entity definitions
@Entity()
export class Player {
@PrimaryKey({ type: t.integer })
id!: number
// Inverse relationship
@OneToMany(() => GameSession, gameSession => gameSession.player, { orphanRemoval: true, hidden: true })
gameSessions = new Collection<GameSession>(this)
}
@Entity({ tableName: 'game_session' })
export class GameSession {
@PrimaryKey({ type: t.integer })
id!: number
@ManyToOne(() => Player, { joinColumn: 'player', nullable: true })
player: Rel<Player> | null = null
// Inverse relationship
@OneToMany(() => GameAction, gameAction => gameAction.gameSession, { orphanRemoval: true })
gameActions = new Collection<GameAction>(this)
}
@Entity({ tableName: 'game_action' })
export class GameAction {
@PrimaryKey({ type: t.integer })
id!: number
@ManyToOne(() => GameSession, { joinColumn: 'game_session', nullable: true })
gameSession: Rel<GameSession>
@Property({ type: t.integer })
value: number
}Code
const qb = (em as SqlEntityManager).createQueryBuilder(Player, 'p')
const query = qb
.select([
raw('max(ga.value) as max'),
raw('avg(ga.value) as avg'),
])
.leftJoin('gameSessions', 'gs')
.leftJoin('gs.gameActions', 'ga')
.where({ id: player.id })
// Workaround to fix created query
// query.getQuery()
const result = await query
.execute<{ max: number | null, avg: number | string | null }>('get')Invalid sql query created with above code by mikro-orm 6.2.x:
select max(ga.value) as max, avg(ga.value) as avg
from `player` as `p`
where `p`.`id` in (
select `p`.`id` from (
select `p`.`id` from `player` as `p`
left join `game_session` as `gs` on `p`.`id` = `gs`.`player`
left join `game_action` as `ga` on `gs`.`id` = `ga`.`game_session`
where `p`.`id` = 1
group by `p`.`id`
limit 1
) as `p`
)
- SQLITE_ERROR: no such column: ga.value"Correct sql query when using:
- miko-orm 6.1.x
- 6.2.x with
query.getQuery() - changing
.execute('get')to.execute('run')or.execute('all')
select max(ga.value) as max, avg(ga.value) as avg
from `player` as `p`
left join `game_session` as `gs` on `p`.`id` = `gs`.`player`
left join `game_action` as `ga` on `gs`.`id` = `ga`.`game_session`
where `p`.`id` = 1What driver are you using?
@mikro-orm/sqlite
MikroORM version
6.2.2
Node.js version
18.18.2
Operating system
Windows
Validations
- Read the Contributing Guidelines.
- Read the docs.
- Check that there isn't already an issue that reports the same bug to avoid creating a duplicate.
- Check that this is a concrete bug. For Q&A open a GitHub Discussion or join our Slack.
- The provided reproduction is a minimal reproducible example of the bug.