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

RequestFXOP•9mo 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.

RequestFXOP•9mo ago

I feel this must be a banger

RequestFXOP•9mo 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•9mo 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.

Gaming

Programming

How to query many-many with mysql