DT
Drizzle Team•12mo ago
RequestFX

How to query many-many with mysql

I setup a simple example to test if many-many query and ran again into the same issue. 
Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(select coalesce(json_arrayagg(json_arrayps`.`user_id`, `u...' at line 1
    at PromisePool.query (C:\Users\Anix\Documents\MyDocuments\Coding\JS\DrizzleTesting\node_modules\mysql2\promise.js:356:22)
    at MySql2PreparedQuery.execute (C:\Users\Anix\Documents\MyDocuments\Coding\JS\DrizzleTesting\node_modules\src\mysql2\session.ts:88:31)
    at QueryPromise.execute (C:\Users\Anix\Documents\MyDocuments\Coding\JS\DrizzleTesting\node_modules\src\mysql-core\query-builders\query.ts:149:25)
    at QueryPromise.then (C:\Users\Anix\Documents\MyDocuments\Coding\JS\DrizzleTesting\node_modules\src\query-promise.ts:31:15)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sql: 'select `users`.`id`, `users`.`name`, `users_usersToGroups`.`data` as `usersToGroups` from `users` left join lateral (select coalesce(json_arrayagg(json_array(`users_usersToGroups`.`user_id`, `users_usersToGroups`.`group_id`, `users_usersToGroups_group`.`data`)), json_array()) as `data` from `users_to_groups` `users_usersToGroups` left join lateral (select json_array(`users_usersToGroups_group`.`id`, `users_usersToGroups_group`.`name`) as `data` from (select * from `groups` `users_usersToGroups_group` where `users_usersToGroups_group`.`id` = 
`users_usersToGroups`.`group_id` limit 1) `users_usersToGroups_group`) `users_usersToGroups_group` on true where `users_usersToGroups`.`user_id` = `users`.`id`) `users_usersToGroups` on true',
  sqlState: '42000',
near '(select coalesce(json_arrayagg(json_array(`users_usersToGroups`.`user_id`, `u...' at line 1"
}
Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(select coalesce(json_arrayagg(json_arrayps`.`user_id`, `u...' at line 1
    at PromisePool.query (C:\Users\Anix\Documents\MyDocuments\Coding\JS\DrizzleTesting\node_modules\mysql2\promise.js:356:22)
    at MySql2PreparedQuery.execute (C:\Users\Anix\Documents\MyDocuments\Coding\JS\DrizzleTesting\node_modules\src\mysql2\session.ts:88:31)
    at QueryPromise.execute (C:\Users\Anix\Documents\MyDocuments\Coding\JS\DrizzleTesting\node_modules\src\mysql-core\query-builders\query.ts:149:25)
    at QueryPromise.then (C:\Users\Anix\Documents\MyDocuments\Coding\JS\DrizzleTesting\node_modules\src\query-promise.ts:31:15)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sql: 'select `users`.`id`, `users`.`name`, `users_usersToGroups`.`data` as `usersToGroups` from `users` left join lateral (select coalesce(json_arrayagg(json_array(`users_usersToGroups`.`user_id`, `users_usersToGroups`.`group_id`, `users_usersToGroups_group`.`data`)), json_array()) as `data` from `users_to_groups` `users_usersToGroups` left join lateral (select json_array(`users_usersToGroups_group`.`id`, `users_usersToGroups_group`.`name`) as `data` from (select * from `groups` `users_usersToGroups_group` where `users_usersToGroups_group`.`id` = 
`users_usersToGroups`.`group_id` limit 1) `users_usersToGroups_group`) `users_usersToGroups_group` on true where `users_usersToGroups`.`user_id` = `users`.`id`) `users_usersToGroups` on true',
  sqlState: '42000',
near '(select coalesce(json_arrayagg(json_array(`users_usersToGroups`.`user_id`, `u...' at line 1"
}

DrizzleTesting.zip

4 Replies
RequestFX
RequestFXOP•12mo ago
I took exactly this many to many schema for many-many and modified it for mysql so this should be fine https://orm.drizzle.team/docs/rqb#many-to-many and this is how I query m-m 
async function main() {
    const usersWithGroups = await DB.query.users.findMany({ with: { usersToGroups: { with: { group: true } } } });
    console.log(`Completed m-m query: ${usersWithGroups}`);
}
async function main() {
    const usersWithGroups = await DB.query.users.findMany({ with: { usersToGroups: { with: { group: true } } } });
    console.log(`Completed m-m query: ${usersWithGroups}`);
}
The rest of the code is rather unrelevant setup code
Drizzle ORM - Query
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
RequestFX
RequestFXOP•12mo ago
I feel this must be a banger
No description
RequestFX
RequestFXOP•12mo ago
it did not help I will just use joins, I think this might not work for mysql but I have no other databases to check if thats the case. Please notify me if thats the case
Sillvva
Sillvva•12mo ago
MariaDB does not support all MySQL features, including the Lateral keyword https://mariadb.com/kb/en/incompatibilities-and-feature-differences-between-mariadb-11-3-and-mysql-8-/
MariaDB KnowledgeBase
Incompatibilities and Feature Differences Between MariaDB 11.3 and ...
List of incompatibilities and feature differences between MariaDB 11.3 and MySQL 8.0.

Did you find this page helpful?