[BUG] Duplicate column when using withCount on a preloaded field

[zecar]

Package version

21.1.0

Describe the bug

Here are my 3 models

export default class Competition extends BaseModel {
  @column({ isPrimary: true })
  declare id: number

  @column()
  declare title: string

  @column()
  declare description: string

  @column({
    serialize: (value: String | null) => {
      return value ? `${Env.get('CDN_URL')}/${value}` : ''
    },
  })
  declare coverUrl: string

  @column({
    serialize: (value: String | null) => {
      return value ? `${Env.get('CDN_URL')}/${value}` : ''
    },
  })
  declare thumbnailUrl: string

  @column()
  declare startingBalance: number

  @column()
  declare refreshBalanceIntervalSeconds: number

  @column()
  declare minBet: number

  @column()
  declare durationSeconds: number

  @column({
    serialize: (value) => Boolean(value),
    consume: (value) => Boolean(value),
  })
  declare recurring: boolean

  @column()
  declare recurringCooldownSeconds: number

  @column()
  declare slug: string

  @column()
  declare gradientColor: string

  @column.dateTime({})
  declare endAt: DateTime | null

  @hasMany(() => CompetitionRound)
  declare rounds: HasMany<typeof CompetitionRound>

  @hasMany(() => RewardGroup)
  declare rewardGroups: HasMany<typeof RewardGroup>

  @column.dateTime({ autoCreate: true })
  declare createdAt: DateTime

  @column.dateTime({ autoCreate: true, autoUpdate: true })
  declare updatedAt: DateTime

  @column.dateTime({})
  declare deletedAt: DateTime | null
}

export default class CompetitionRound extends BaseModel {
  @column({ isPrimary: true })
  declare id: number

  @column()
  declare competitionId: number

  @belongsTo(() => Competition)
  declare competition: BelongsTo<typeof Competition>

  @hasMany(() => CompetitionRoundPlayer)
  declare players: HasMany<typeof CompetitionRoundPlayer>

  @column.dateTime({ autoCreate: true })
  declare startedAt: DateTime

  @column.dateTime({})
  declare endedAt: DateTime

  @column.dateTime({})
  declare lastBalanceRefreshAt: DateTime
}

export default class CompetitionRoundPlayer extends BaseModel {
  @column({ isPrimary: true })
  declare id: number

  @column()
  declare competitionRoundId: number

  @belongsTo(() => CompetitionRound)
  declare round: BelongsTo<typeof CompetitionRound>

  @column()
  declare userId: number

  @belongsTo(() => User)
  declare user: BelongsTo<typeof User>

  @column()
  declare balance: number

  @column()
  declare pointsEarned: number

  @hasMany(() => DistributedCompetitionReward)
  declare distributedCompetitionRewards: HasMany<typeof DistributedCompetitionReward>

  @column.dateTime({ autoCreate: true })
  declare enteredAt: DateTime
}

Here is the query i'm trying to run:

const competitions = await Competition.query()
      .preload('rounds', (r) => r.orderBy('id', 'desc').withCount('players').groupLimit(1))
      .orderBy('id', 'desc')
      .exec()

And here is the issue:

select *
from (
    select `competition_rounds`.*, `competition_id`, (
        select count(*) from `competition_round_players` where `competition_rounds`.`id` = `competition_round_players`.`competition_round_id`
    ) as `players_count`, row_number() over (PARTITION BY competition_id ORDER BY id desc) as adonis_group_limit_counter
    from `competition_rounds`
    where `competition_id` in (28, 27, 26, 25, 24, 23, 22, 21, 20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 1)
    order by `id` desc
    ) as `adonis_temp`
where `adonis_group_limit_counter` <= 1

- Duplicate column name 'competition_id'

After a bit of debugging, i saw that introducing withCount will duplicate the competition_id column because it adds it after select competition_rounds.*

That, combined with groupLimit will cause the whole query to be surrounded by a select * from (select....)

But the main issue is the duplication of the competition_id column

Reproduction repo

No response

[zecar]

there is a duplicated issue i just saw: https://github.com/adonisjs/lucid/issues/933