Can't preload relations for multiple rows

[DraftProducts]

Hi there 👋

I tried to preload quizzes for multiple users, but the query only return quizzes for the first user, did I misuse the preload() method ?

 const activeUsers = await User.query()
      .whereHas('quizzes', (query) => {
        query.where('status', 1)
      })
      .preload('quizzes', (query) =>
        query
          .where('status', 1)
          .preload('domain', (domainQuery) => domainQuery.preload('icon'))
          .limit(5)
      )

As you can see, I got quizzes for the first user but not for the second, which have active quizzes in database 🤔

[thetutlage]

Answered here #3241 (comment)

[DraftProducts]

Thanks

[DraftProducts]

It looks like that this method isn't compatible with MySQL 5.7

[1634399752544] ERROR (bio2game_backend/32 on feedeabfb0e0): select * from (select *, row_number() over (PARTITION BY contributor_id ORDER BY id desc) as adonis_group_limit_counter from `quizzes` where (`status` = 1) and (`contributor_id` in (1, 16, 18, 120, 139, 143, 183, 188, 195, 198, 201, 207, 210, 211, 212))) as `adonis_temp` where `adonis_group_limit_counter` <= 5 - ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY contributor_id ORDER BY id desc) as adonis_group_limit_counter fro' at line 1
    err: {
      "type": "Error",
      "message": "select * from (select *, row_number() over (PARTITION BY contributor_id ORDER BY id desc) as adonis_group_limit_counter from `quizzes` where (`status` = 1) and (`contributor_id` in (1, 16, 18, 120, 139, 143, 183, 188, 195, 198, 201, 207, 210, 211, 212))) as `adonis_temp` where `adonis_group_limit_counter` <= 5 - ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY contributor_id ORDER BY id desc) as adonis_group_limit_counter fro' at line 1",
      "stack":
          Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY contributor_id ORDER BY id desc) as adonis_group_limit_counter fro' at line 1
              at Query.Sequence._packetToError (/app/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
              at Query.ErrorPacket (/app/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
              at Protocol._parsePacket (/app/node_modules/mysql/lib/protocol/Protocol.js:291:23)
              at Parser._parsePacket (/app/node_modules/mysql/lib/protocol/Parser.js:433:10)
              at Parser.write (/app/node_modules/mysql/lib/protocol/Parser.js:43:10)
              at Protocol.write (/app/node_modules/mysql/lib/protocol/Protocol.js:38:16)
              at Socket.<anonymous> (/app/node_modules/mysql/lib/Connection.js:88:28)
              at Socket.<anonymous> (/app/node_modules/mysql/lib/Connection.js:526:10)
              at Socket.emit (events.js:400:28)
              at addChunk (internal/streams/readable.js:290:12)
              --------------------
              at Protocol._enqueue (/app/node_modules/mysql/lib/protocol/Protocol.js:144:48)
              at Connection.query (/app/node_modules/mysql/lib/Connection.js:198:25)
              at /app/node_modules/knex/lib/dialects/mysql/index.js:126:18
              at new Promise (<anonymous>)
              at Client_MySQL._query (/app/node_modules/knex/lib/dialects/mysql/index.js:120:12)
              at executeQuery (/app/node_modules/knex/lib/execution/internal/query-executioner.js:37:17)
              at Client_MySQL.query (/app/node_modules/knex/lib/client.js:134:12)
              at Runner.query (/app/node_modules/knex/lib/execution/runner.js:130:36)
              at ensureConnectionCallback (/app/node_modules/knex/lib/execution/internal/ensure-connection-callback.js:13:17)
              at Runner.ensureConnection (/app/node_modules/knex/lib/execution/runner.js:254:14)
      "code": "ER_PARSE_ERROR",
      "errno": 1064,
      "sqlMessage": "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY contributor_id ORDER BY id desc) as adonis_group_limit_counter fro' at line 1",
      "sqlState": "42000",
      "index": 0,
      "sql": "select * from (select *, row_number() over (PARTITION BY contributor_id ORDER BY id desc) as adonis_group_limit_counter from `quizzes` where (`status` = 1) and (`contributor_id` in (1, 16, 18, 120, 139, 143, 183, 188, 195, 198, 201, 207, 210, 211, 212))) as `adonis_temp` where `adonis_group_limit_counter` <= 5",
      "status": 500
    }

[RomainLanz]

MySQL 5.7 does not support SQL Window functions.

Upgrade or change your DBMS.

[thetutlage]

Yeah, looks like the case. Coz we are also skipping tests on mysql 5.7 for groupLimit https://github.com/adonisjs/lucid/blob/develop/test/orm/model-has-many.spec.ts#L3056.

I think we should update the docs with a warning