AuroPick Posts - Answer Overflow

AuroPick

Posts Comments

DTDrizzle Team

•Created by AuroPick on 4/7/2024 in #help

Query API relation null type

4 replies

DTDrizzle Team

•Created by AuroPick on 3/2/2024 in #help

How to use TABLESAMPLE

Hi, I want to select random rows from a table You can achieve this using TABLESAMPLE but there is no resource on how to use this in drizzle. Is this supported or can I implement this by using sql operator reference: https://wiki.postgresql.org/wiki/TABLESAMPLE_Implementation

3 replies

DTDrizzle Team

•Created by AuroPick on 2/22/2024 in #help

How to create two-way unique index or constraint

lets say I will have a matches table

export const matches = pgTable(
  'matches',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('userId')
      .references(() => users.id)
      .notNull(),
    swipedUserId: uuid('swiped_user_id')
      .references(() => users.id)
      .notNull(),
    type: swipeTypeEnum('type').notNull().default('normal'),
    createdAt: timestamp('created_at').notNull().defaultNow(),
    updatedAt: timestamp('updated_at').notNull().defaultNow(),
    deletedAt: timestamp('deleted_at')
  },
  (table) => ({
    uniqueUserIdSwipedUserId: unique().on(table.userId, table.swipedUserId),
    userIdIdx: index('user_id_idx').on(table.userId),
    swipedUserIdIdx: index('swiped_user_id_idx').on(table.swipedUserId)
  })
)

export const matches = pgTable(
  'matches',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('userId')
      .references(() => users.id)
      .notNull(),
    swipedUserId: uuid('swiped_user_id')
      .references(() => users.id)
      .notNull(),
    type: swipeTypeEnum('type').notNull().default('normal'),
    createdAt: timestamp('created_at').notNull().defaultNow(),
    updatedAt: timestamp('updated_at').notNull().defaultNow(),
    deletedAt: timestamp('deleted_at')
  },
  (table) => ({
    uniqueUserIdSwipedUserId: unique().on(table.userId, table.swipedUserId),
    userIdIdx: index('user_id_idx').on(table.userId),
    swipedUserIdIdx: index('swiped_user_id_idx').on(table.swipedUserId)
  })
)

in this case user retries to match swipedUser. It wont work. but I want to avoid data insert if swipedUser tries to insert match with userId I found this on stackoverflow but don't know how to implement this in drizzle

create table friendz (
  from_id int,
  to_id int
);

create unique index ifriendz on friendz(greatest(from_id,to_id), least(from_id,to_id));

create table friendz (
  from_id int,
  to_id int
);

create unique index ifriendz on friendz(greatest(from_id,to_id), least(from_id,to_id));

summary if there is a data like this userId: 1 swipedUserId: 2 and when to try insert userId: 2 swipedUserId: 1 I should get error

1 replies

DTDrizzle Team

•Created by AuroPick on 2/19/2024 in #help

How should I store checkbox values in postgres

let say I have a checkbox list called interests and user checked some options. They will send request like this: interests: ['cars', 'hiking', 'dance'] and later on users will be able to change interests. lets say user unchecked hiking, new put request will be like this interests: ['cars', 'dance'] how should I store this data? seperate table?, simple array column? If I create seperate table, schema will be like this id: uuid userId: uuid reference user id interest: interest enum so when user send put request what should I do? delete all existing interests and insert new ones? is this normal way? sorry i am a sql noob

5 replies

Gaming

Programming