How to query many-many with mysql

RRequestFX3/31/2024
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"
}
RRequestFX3/31/2024
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.
RRequestFX3/31/2024
I feel this must be a banger
No description
RRequestFX3/31/2024
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
SSillvva3/31/2024
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.

Looking for more? Join the community!

Want results from more Discord servers?
Add your server
Recommended Posts
How do I access config in fromDriver and toDriver when defining a custom type using customType?None of the examples do this so it's not clear how/if this is possible?Cannot get user roleI'm using Next.js with next auth and the drizzle adapter. I must be doing something wrong because th`drizzle-kit generate:sqlite` doesn't work`lib/drizzle.ts` ```ts import { text, blob, sqliteTable } from "drizzle-orm/sqlite-core"; export coStudio not enough info to infer relationI'm getting this error: ``` throw new Error( ^ Error: There is not enough information toTurso DB reads (alot)Using turso for a project with drizzle ( I usually use mysql ), and i'm getting way more database reAppending to array OnConflictUpdate()? (Postgres)**full question on stack overflow: **https://stackoverflow.com/questions/78246191/error-appending-toTypeError: Cannot read properties of undefined (reading 'compositePrimaryKeys')I'm getting this error when I want to rename a table (there are no other changes). Old table: ``` ebetter way to check truthy / falsey from selectHey all, currently checking if the length > 0 and <1 to check truthy / falsey is there a better way Relation join conditionsHey - let's say I have the following SQL: ```sql SELECT blocks.id, blocks.type FROM inHow do I use the sql operator with better-sqlite3?Hello. I'm trying to build a custom query with the sql operator in drizzle-orm, and to run it againsIs there a way in drizzle to return the count before limit and offset?i have a table that i need to limit and offset that also needs to return the count. is there a way torder by dynamic columi have a table and i want the user to be able to order by different columns. how do i write the driz