DT
Drizzle Team•2w ago
CyberCipher

How to query join table with db.query syntax?

Here is my schema for User, Library and LibraryUser tables.. 
import { relations } from "drizzle-orm";
import { uniqueIndex, uuid, varchar } from "drizzle-orm/pg-core";
import { LibraryAlbum } from "./library-album";
import { LibraryArtist } from "./library-artist";
import { LibraryPath } from "./library-path";
import { LibraryTrack } from "./library-track";
import { LibraryUser } from "./library-user";
import { schema } from "./schema";

export const Library = schema.table("library", {
    id: uuid("id").primaryKey().notNull().unique().defaultRandom(),
    name: varchar("name", { length: 255 }).notNull(),
}, (table) => {
    return {
        library_index: uniqueIndex("library_index").on(table.name),
    };
});

export const LibraryRelation = relations(Library, ({ many }) => ({
    users: many(LibraryUser),
    paths: many(LibraryPath),
    tracks: many(LibraryTrack),
    albums: many(LibraryAlbum),
    artists: many(LibraryArtist),
}));
import { relations } from "drizzle-orm";
import { uniqueIndex, uuid, varchar } from "drizzle-orm/pg-core";
import { LibraryAlbum } from "./library-album";
import { LibraryArtist } from "./library-artist";
import { LibraryPath } from "./library-path";
import { LibraryTrack } from "./library-track";
import { LibraryUser } from "./library-user";
import { schema } from "./schema";

export const Library = schema.table("library", {
    id: uuid("id").primaryKey().notNull().unique().defaultRandom(),
    name: varchar("name", { length: 255 }).notNull(),
}, (table) => {
    return {
        library_index: uniqueIndex("library_index").on(table.name),
    };
});

export const LibraryRelation = relations(Library, ({ many }) => ({
    users: many(LibraryUser),
    paths: many(LibraryPath),
    tracks: many(LibraryTrack),
    albums: many(LibraryAlbum),
    artists: many(LibraryArtist),
}));
import { relations } from "drizzle-orm";
import { primaryKey, uuid } from "drizzle-orm/pg-core";
import { Library } from "./library";
import { schema } from "./schema";
import { User } from "./users";

export const LibraryUser = schema.table("library_user", {
    library_id: uuid("library_id").references(() => Library.id, { onDelete: "cascade" }).notNull(),
    user_id: uuid("user_id").references(() => User.id, { onDelete: "cascade" }).notNull(),
}, (table) => ({
    pk: primaryKey({ columns: [table.user_id, table.library_id] }),
}));

export const LibraryUserRelation = relations(LibraryUser, ({ one }) => ({
    library: one(Library, {
        fields: [LibraryUser.library_id],
        references: [Library.id],
    }),
    user: one(User, {
        fields: [LibraryUser.user_id],
        references: [User.id],
    }),
}));
import { relations } from "drizzle-orm";
import { primaryKey, uuid } from "drizzle-orm/pg-core";
import { Library } from "./library";
import { schema } from "./schema";
import { User } from "./users";

export const LibraryUser = schema.table("library_user", {
    library_id: uuid("library_id").references(() => Library.id, { onDelete: "cascade" }).notNull(),
    user_id: uuid("user_id").references(() => User.id, { onDelete: "cascade" }).notNull(),
}, (table) => ({
    pk: primaryKey({ columns: [table.user_id, table.library_id] }),
}));

export const LibraryUserRelation = relations(LibraryUser, ({ one }) => ({
    library: one(Library, {
        fields: [LibraryUser.library_id],
        references: [Library.id],
    }),
    user: one(User, {
        fields: [LibraryUser.user_id],
        references: [User.id],
    }),
}));
9 Replies
CyberCipher
CyberCipherOP•2w ago
import { relations } from "drizzle-orm";
import { text, timestamp, uniqueIndex, uuid, varchar } from "drizzle-orm/pg-core";
import { LibraryUser } from "./library-user";
import { Playlist } from "./playlist";
import { schema } from "./schema";
import { UserTrackLikes } from "./user-track-likes";
import { UserTrackPlays } from "./user-track-plays";

export const User = schema.table("user", {
    id: uuid("id").primaryKey().notNull().unique().defaultRandom(),
    username: varchar("username", { length: 255 }).notNull().unique(),
    email: varchar("email", { length: 255 }).notNull(),
    token: varchar("token", { length: 255 }).unique(),
    hashed_password: varchar("hashed_password", { length: 255 }).unique().notNull(),
    avatar_url: varchar("avatar_url", { length: 255 }),
    role: text("role").array().default(["User"]).notNull(),
    last_login: timestamp("last_login", { withTimezone: true }),
    created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
}, (table) => {
    return {
        user_index: uniqueIndex("user_index").on(table.email)
    }
});

export const UserRelation = relations(User, ({ many }) => {
    return {
        libraries: many(LibraryUser),
        playlists: many(Playlist),
        track_likes: many(UserTrackLikes),
        track_plays: many(UserTrackPlays),
    }
});
import { relations } from "drizzle-orm";
import { text, timestamp, uniqueIndex, uuid, varchar } from "drizzle-orm/pg-core";
import { LibraryUser } from "./library-user";
import { Playlist } from "./playlist";
import { schema } from "./schema";
import { UserTrackLikes } from "./user-track-likes";
import { UserTrackPlays } from "./user-track-plays";

export const User = schema.table("user", {
    id: uuid("id").primaryKey().notNull().unique().defaultRandom(),
    username: varchar("username", { length: 255 }).notNull().unique(),
    email: varchar("email", { length: 255 }).notNull(),
    token: varchar("token", { length: 255 }).unique(),
    hashed_password: varchar("hashed_password", { length: 255 }).unique().notNull(),
    avatar_url: varchar("avatar_url", { length: 255 }),
    role: text("role").array().default(["User"]).notNull(),
    last_login: timestamp("last_login", { withTimezone: true }),
    created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
}, (table) => {
    return {
        user_index: uniqueIndex("user_index").on(table.email)
    }
});

export const UserRelation = relations(User, ({ many }) => {
    return {
        libraries: many(LibraryUser),
        playlists: many(Playlist),
        track_likes: many(UserTrackLikes),
        track_plays: many(UserTrackPlays),
    }
});
I wanna get the all libraries for a user.. 
export async function get_all_libraries_for_user(user_id: string) {
    const libraries = await db.query.Library.findMany({
        with: {
            users: true,
            paths: true
        },
        where: eq(LibraryUser.user_id, user_id)
    });

    return libraries;
}
export async function get_all_libraries_for_user(user_id: string) {
    const libraries = await db.query.Library.findMany({
        with: {
            users: true,
            paths: true
        },
        where: eq(LibraryUser.user_id, user_id)
    });

    return libraries;
}
this doesn't work, 
PostgresError: column Library.user_id does not exist
    at ErrorResponse (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:788:26)
    at handle (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:474:6)
    at Socket.data (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:128:17)
PostgresError: column Library.user_id does not exist
    at ErrorResponse (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:788:26)
    at handle (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:474:6)
    at Socket.data (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:128:17)
can db.query do this kind of query? do I have to use db.select() and leftJoin or something? This works, I would like to db.query though if possible. 
export async function get_all_libraries_for_user(user_id: string) {
    const libraries = await db
        .select()
        .from(Library)
        .innerJoin(LibraryUser, eq(Library.id, LibraryUser.library_id))
        .where(eq(LibraryUser.user_id, user_id));

    return libraries;
}
export async function get_all_libraries_for_user(user_id: string) {
    const libraries = await db
        .select()
        .from(Library)
        .innerJoin(LibraryUser, eq(Library.id, LibraryUser.library_id))
        .where(eq(LibraryUser.user_id, user_id));

    return libraries;
}
Xpecial Poo
Xpecial Poo•2w ago
are passing the relations into the schema property?
CyberCipher
CyberCipherOP•2w ago
hmm? 
    const libraries = await db.query.Library.findMany({
        with: {
            users: {
                where: eq(LibraryUser.user_id, user_id)
            },
            paths: true
        },
    });
    const libraries = await db.query.Library.findMany({
        with: {
            users: {
                where: eq(LibraryUser.user_id, user_id)
            },
            paths: true
        },
    });
this is not correct either..
Angelelz
Angelelz•2w ago
You can't filter by a related table The workaround is to use a subquery Take a look at this post for an example https://discord.com/channels/1043890932593987624/1303405402230100069
CyberCipher
CyberCipherOP•2w ago
I didn't see any example in the post. Is db.query worth it though? I always used db.select but was trying out this recently, at first it seemed simple, but I think I'll stick with db.select because complex queries like, 
    const libraries = await db
        .select({
            library: Library,
            artist: Artist,
            album: Album
        })
        .from(Library)
        .leftJoin(LibraryArtist, eq(Library.id, LibraryArtist.library_id))
        .leftJoin(Artist, eq(LibraryArtist.artist_id, Artist.id))
        .leftJoin(LibraryAlbum, eq(Library.id, LibraryAlbum.library_id))
        .leftJoin(Album, eq(LibraryAlbum.album_id, Album.id))
        .leftJoin(LibraryUser, eq(Library.id, LibraryUser.library_id))
        .where(eq(LibraryUser.user_id, user_id))
    const libraries = await db
        .select({
            library: Library,
            artist: Artist,
            album: Album
        })
        .from(Library)
        .leftJoin(LibraryArtist, eq(Library.id, LibraryArtist.library_id))
        .leftJoin(Artist, eq(LibraryArtist.artist_id, Artist.id))
        .leftJoin(LibraryAlbum, eq(Library.id, LibraryAlbum.library_id))
        .leftJoin(Album, eq(LibraryAlbum.album_id, Album.id))
        .leftJoin(LibraryUser, eq(Library.id, LibraryUser.library_id))
        .where(eq(LibraryUser.user_id, user_id))
I don't know how you would do that with db.query thing. unless there is some benefit performance wise or something.. I think I'll stick with this. this makes more sense when reading the query as well.
Xpecial Poo
Xpecial Poo•2w ago
i meant, when you are creating the db client. Are you passing the relations + schema into it? i noticed that if i didnt put those relations into it, the db.query would never work. example: 
export const db = drizzle({
  client: createPool(),
  logger: true,
  schema: { ...schema, ...relations },
})
export const db = drizzle({
  client: createPool(),
  logger: true,
  schema: { ...schema, ...relations },
})
CyberCipher
CyberCipherOP•2w ago
Yes, I pass both.
Angelelz
Angelelz•2w ago
CyberCipher
CyberCipherOP•2w ago
I see. I don't like this, it feels like a hack, I don't think this should be in a release but rather in a testing or dev branch. I think I'm gonna stick to db.select().
Want results from more Discord servers?
Add your server