finn Posts - Answer Overflow

finn

•Created by finn on 6/23/2023 in #help

different `where` but same return type for relational queries?

what's the best way to approach it when i have a fair few relational queries that have different where: clauses, but are otherwise the same (or extremely similar)?

public static async queryPostsByWantsItemId(itemId: number) {
  return await db.query.posts.findMany({
    /* different v */
    where: (post, { eq, and }) => exists(
      db.select({}).from(postItemsWants).limit(1).where(
        and(
          eq(postItemsWants.postId, post.id),
          eq(postItemsWants.itemId, itemId)
        )
      )
    ),
    /* different ^ */
    /* same v */
    limit: 50,
    orderBy: (post, { desc }) => [desc(post.createdAt)],
    columns: {
      id: true,
      description: true,
      createdAt: true,
      expiresAt: true,
    },
    with: {
      // ...
    }
    /* same ^ */
  });
}

public static async queryPostsByWantsItemId(itemId: number) {
  return await db.query.posts.findMany({
    /* different v */
    where: (post, { eq, and }) => exists(
      db.select({}).from(postItemsWants).limit(1).where(
        and(
          eq(postItemsWants.postId, post.id),
          eq(postItemsWants.itemId, itemId)
        )
      )
    ),
    /* different ^ */
    /* same v */
    limit: 50,
    orderBy: (post, { desc }) => [desc(post.createdAt)],
    columns: {
      id: true,
      description: true,
      createdAt: true,
      expiresAt: true,
    },
    with: {
      // ...
    }
    /* same ^ */
  });
}

11 replies

DTDrizzle Team

•Created by finn on 6/22/2023 in #help

re-formatting duplicated data

21 replies

DTDrizzle Team

•Created by finn on 6/21/2023 in #help

nested relation queries

9 replies

DTDrizzle Team

•Created by finn on 6/20/2023 in #help

`where` within `where`? confused about complex query

export const posts = pgTable('posts', {
  id: uuid('id').defaultRandom().primaryKey(),
  userId: char('user_id', { length: 17 }).notNull().references(() => users.id),
  description: text('description'),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
});

export const postsRelations = relations(posts, ({ one, many }) => ({
  user: one(users, {
    fields: [posts.userId],
    references: [users.id],
  }),
  userItems: many(postUserItems),
}));

export const userItems = pgTable('user_items', {
  assetId: varchar('asset_id', { length: 255 }).primaryKey(),
  userId: char('user_id', { length: 17 }).notNull().references(() => users.id),
  itemId: serial('item_id').references(() => items.id).notNull(),
  imageUrl: varchar('image_url', { length: 512 }).notNull(),
});

export const userItemsRelations = relations(userItems, ({ one, many }) => ({
  user: one(users, {
    fields: [userItems.userId],
    references: [users.id],
  }),
  item: one(items, {
    fields: [userItems.itemId],
    references: [items.id],
  }),
  posts: many(trades),
  postUserItems: many(postUserItems),
}));

export const postUserItems = pgTable('post_user_items', {
  postId: uuid('post_id').references(() => posts.id),
  assetId: varchar('asset_id', { length: 255 }).references(() => userItems.assetId),
},(table) => {
  return {
    pk: primaryKey(table.postId, table.assetId),
  };
});

export const postUserItemsRelations = relations(postUserItems, ({ one, many }) => ({
  post: one(posts, {
    fields: [postUserItems.postId],
    references: [posts.id],
  }),
  userItem: one(userItems, {
    fields: [postUserItems.assetId],
    references: [userItems.assetId],
  }),
}));

export const posts = pgTable('posts', {
  id: uuid('id').defaultRandom().primaryKey(),
  userId: char('user_id', { length: 17 }).notNull().references(() => users.id),
  description: text('description'),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
});

export const postsRelations = relations(posts, ({ one, many }) => ({
  user: one(users, {
    fields: [posts.userId],
    references: [users.id],
  }),
  userItems: many(postUserItems),
}));

export const userItems = pgTable('user_items', {
  assetId: varchar('asset_id', { length: 255 }).primaryKey(),
  userId: char('user_id', { length: 17 }).notNull().references(() => users.id),
  itemId: serial('item_id').references(() => items.id).notNull(),
  imageUrl: varchar('image_url', { length: 512 }).notNull(),
});

export const userItemsRelations = relations(userItems, ({ one, many }) => ({
  user: one(users, {
    fields: [userItems.userId],
    references: [users.id],
  }),
  item: one(items, {
    fields: [userItems.itemId],
    references: [items.id],
  }),
  posts: many(trades),
  postUserItems: many(postUserItems),
}));

export const postUserItems = pgTable('post_user_items', {
  postId: uuid('post_id').references(() => posts.id),
  assetId: varchar('asset_id', { length: 255 }).references(() => userItems.assetId),
},(table) => {
  return {
    pk: primaryKey(table.postId, table.assetId),
  };
});

export const postUserItemsRelations = relations(postUserItems, ({ one, many }) => ({
  post: one(posts, {
    fields: [postUserItems.postId],
    references: [posts.id],
  }),
  userItem: one(userItems, {
    fields: [postUserItems.assetId],
    references: [userItems.assetId],
  }),
}));

how can i select posts that contain a userItems with assetId = {X}?

13 replies

DTDrizzle Team

•Created by finn on 6/19/2023 in #help

types from nested relational queries

wondering what the type of user is?

async function getUserWithSettings(steamId: string): Promise<User> {
  const user = await db.query.users.findFirst({
    where: (user, { eq }) => eq(user.steamId, steamId),
    with: {
      settings: {
        columns : {
          steamId: false,
        },
      },
    }
  });
  return user;
}

async function getUserWithSettings(steamId: string): Promise<User> {
  const user = await db.query.users.findFirst({
    where: (user, { eq }) => eq(user.steamId, steamId),
    with: {
      settings: {
        columns : {
          steamId: false,
        },
      },
    }
  });
  return user;
}

1 replies

DTDrizzle Team

•Created by finn on 6/19/2023 in #help

Composite Primary Key from Foreign Keys

export const userItemsHas = pgTable('user_items_has', {
  postId: uuid('post_id').references(() => posts.id),
  userItemId: serial("user_item_id").references(() => userItems.id),
},(table) => {
  return {
    pk: primaryKey(table.postId, table.userItemId),
  };
});

export const userItemsHas = pgTable('user_items_has', {
  postId: uuid('post_id').references(() => posts.id),
  userItemId: serial("user_item_id").references(() => userItems.id),
},(table) => {
  return {
    pk: primaryKey(table.postId, table.userItemId),
  };
});

Error: Failed to run sql query: multiple primary keys for table "user_items_has" are not allowed

5 replies

DTDrizzle Team

•Created by finn on 6/18/2023 in #help

createdAt and expiresAt defaults

Is this the best way?

createdAt: timestamp('created_at', { withTimezone: true }).defaultNow(),
expiresAt: timestamp('expires_at', { withTimezone: true }).default(sql`(now() + interval '7 days')`),

createdAt: timestamp('created_at', { withTimezone: true }).defaultNow(),
expiresAt: timestamp('expires_at', { withTimezone: true }).default(sql`(now() + interval '7 days')`),

2 replies

Gaming

Programming