Query: How to Get 3 Top Score

[kicut]

I'm learning Adonis by making a simple game, I'm confused how to get the 3 highest scores for the last 24 hours based on wpm and accuracy.

This is the query I have tried:

const levels = await Level.query().preload('tests', (queryTests) => {
  queryTests.preload('language').preload('scores', (queryScores) => {
    queryScores
      .where('created_at', '>=', new Date(new Date().getTime() - 24 * 60 * 60 * 1000))
      .orderBy('wpm', 'desc')
      .orderBy('accuracy', 'desc')
      .preload('user')
      .limit(3)
  })
})

The above query doesn't work as I expected.

Each user can have multiple scores, so each user must have one score, namely the score with the highest wpm value and accuracy in the last 24 hours. Then, I want to sort them by wpm and accuracy, and take the top 3 scores.

This is the model I made.

Level

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

  @column()
  public name: string

  @hasMany(() => Test)
  public tests: HasMany<typeof Test>

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

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

Test

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

  @column()
  public words: string

  @column()
  public languageId: number

  @belongsTo(() => Language)
  public language: BelongsTo<typeof Language>

  @column()
  public levelId: number

  @belongsTo(() => Level)
  public level: BelongsTo<typeof Level>

  @hasMany(() => Score)
  public scores: HasMany<typeof Score>

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

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

  @beforeSave()
  public static async hashPassword(user: Test) {
    if (user.$dirty.words) {
      user.words = JSON.stringify(user.words)
    }
  }
}

User

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

  @column()
  public name: string

  @hasMany(() => Score)
  public scores: HasMany<typeof Score>

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

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

Score

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

  @column()
  public accuracy: number

  @column()
  public wpm: number

  @column()
  public testId: number | null

  @belongsTo(() => Test)
  public test: BelongsTo<typeof Test>

  @column()
  public userId: number

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

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

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

I've done it with Prisma Orm but I'm trying to switch completely to Adonis, it's working fine in Prisma:

const levels = await prisma.level.findMany({
    include: {
      tests: {
        include: {
          language: true,
          scores: {
            include: {
              user: true,
            },
            distinct: ["userId"],
            take: 3,
            orderBy: [
              {
                wpm: "desc",
              },
              {
                accuracy: "desc",
              },
            ],
          },
        },
      },
    },
  });

I've been looking for a way to do this all day long but I haven't found what I want, I really appreciate any answer, thanks in advance .