RequestFX Comments - Answer Overflow

Topics

RequestFX

•Created by RequestFX on 3/31/2024 in #help

How to query many-many with mysql

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

6 replies

•Created by RequestFX on 3/31/2024 in #help

How to query many-many with mysql

it did not help

6 replies

•Created by RequestFX on 3/31/2024 in #help

How to query many-many with mysql

No description

6 replies

•Created by RequestFX on 3/31/2024 in #help

How to query many-many with mysql

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

6 replies

•Created by 1girl, best quality on 3/31/2024 in #help

`drizzle-kit generate:sqlite` doesn't work

Does it maybe not find the path? Maybe it already generated a schema, on no changes to the schema it will not generate a new patch

13 replies

•Created by 1girl, best quality on 3/31/2024 in #help

`drizzle-kit generate:sqlite` doesn't work

Not sure if that's important but I named the table the same as the variable so try lowercase const users

13 replies

•Created by 1girl, best quality on 3/31/2024 in #help

`drizzle-kit generate:sqlite` doesn't work

Did you try with providing a path rather than using a config file? Not sure why you would want to use it, you can use scripts to achieve the same

13 replies

•Created by 1girl, best quality on 3/31/2024 in #help

`drizzle-kit generate:sqlite` doesn't work

Or this video, not SQLite but not relevant for your question https://youtu.be/hIYNOiZXQ7Y

13 replies

•Created by 1girl, best quality on 3/31/2024 in #help

`drizzle-kit generate:sqlite` doesn't work

It says it, no config path provided. A drizzle config is not strictly required. Refer to documentation

13 replies

•Created by Heisnberg on 3/23/2024 in #help

Issues with the generated sql files

schema.ts (pretty sure thats correct, pasted only relevant info)

export const user = mysqlTable("user", {
    id: int("id").primaryKey().autoincrement(),
});

export const game_event = mysqlTable("game_event", {
    id: int("id").primaryKey().autoincrement(),
    fk_host_user: int("fk_host_user").references(() => user.id).notNull(),
});

// m-m relations
export const userToGameEvent = mysqlTable('user_to_game_event', {
    fk_user: int('fk_user').references(() => user.id).notNull(),
    fk_event: int('fk_event').references(() => game_event.id).notNull(),
}, (t) => { return { pk: primaryKey({ columns: [t.fk_user, t.fk_event] }) } }
);

export const userRelations = relations(user, ({ one, many }) => ({
    userToGameEvent: many(userToGameEvent),
}));

export const game_eventRelations = relations(game_event, ({ one, many }) => ({
    userToGameEvent: many(userToGameEvent),
}));

export const userToGameEventRelations = relations(userToGameEvent, ({ one }) => ({
    game_event: one(game_event, {
        fields: [userToGameEvent.fk_event],
        references: [game_event.id],
    }),
    user: one(user, {
        fields: [userToGameEvent.fk_user],
        references: [user.id],
    }),
}));

export const user = mysqlTable("user", {
    id: int("id").primaryKey().autoincrement(),
});

export const game_event = mysqlTable("game_event", {
    id: int("id").primaryKey().autoincrement(),
    fk_host_user: int("fk_host_user").references(() => user.id).notNull(),
});

// m-m relations
export const userToGameEvent = mysqlTable('user_to_game_event', {
    fk_user: int('fk_user').references(() => user.id).notNull(),
    fk_event: int('fk_event').references(() => game_event.id).notNull(),
}, (t) => { return { pk: primaryKey({ columns: [t.fk_user, t.fk_event] }) } }
);

export const userRelations = relations(user, ({ one, many }) => ({
    userToGameEvent: many(userToGameEvent),
}));

export const game_eventRelations = relations(game_event, ({ one, many }) => ({
    userToGameEvent: many(userToGameEvent),
}));

export const userToGameEventRelations = relations(userToGameEvent, ({ one }) => ({
    game_event: one(game_event, {
        fields: [userToGameEvent.fk_event],
        references: [game_event.id],
    }),
    user: one(user, {
        fields: [userToGameEvent.fk_user],
        references: [user.id],
    }),
}));

Query

const gameEvents = await DB.query.user.findMany({ with: { userToGameEvent: { with: { game_event: true } } }});

const gameEvents = await DB.query.user.findMany({ with: { userToGameEvent: { with: { game_event: true } } }});

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_array(`user_userToGameEvent`.`fk_user`, `...' at line 1"

"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_array(`user_userToGameEvent`.`fk_user`, `...' at line 1"

mysql Ver 15.1 Distrib 10.4.32-MariaDB, for Win64 (AMD64), source revision c4143f909528e3fab0677a28631d10389354c491

10 replies

•Created by Heisnberg on 3/23/2024 in #help

Issues with the generated sql files

I get similar error, im trying to query a m-m relation

10 replies