Martacus Comments - Answer Overflow

Martacus

Explore posts from servers

•Created by Martacus on 11/12/2023 in #help

Having trouble writing my sql statement in drizzle. Want to join two variables

  const entityOneAlias = alias(entities, 'entityOne');
  const entityTwoAlias = alias(entities, 'entityTwo');
  
  const data = await db.select({
    id: entityRelations.id,
    name: entityRelations.name,
    entityOne: entityRelations.entityOne,
    entityTwo: entityRelations.entityTwo,
    createdAt: entityRelations.createdAt,
    entityOneName: entityOneAlias.name,
    entityTwoName: entityTwoAlias.name,
  }).from(entityRelations).where(or(
    eq(entityRelations.entityOne, id),
    eq(entityRelations.entityTwo, id)
  ))
  .leftJoin(entityOneAlias, eq(entityOneAlias.id, entityRelations.entityOne))
  .leftJoin(entityTwoAlias, eq(entityTwoAlias.id, entityRelations.entityTwo));

  const entityOneAlias = alias(entities, 'entityOne');
  const entityTwoAlias = alias(entities, 'entityTwo');
  
  const data = await db.select({
    id: entityRelations.id,
    name: entityRelations.name,
    entityOne: entityRelations.entityOne,
    entityTwo: entityRelations.entityTwo,
    createdAt: entityRelations.createdAt,
    entityOneName: entityOneAlias.name,
    entityTwoName: entityTwoAlias.name,
  }).from(entityRelations).where(or(
    eq(entityRelations.entityOne, id),
    eq(entityRelations.entityTwo, id)
  ))
  .leftJoin(entityOneAlias, eq(entityOneAlias.id, entityRelations.entityOne))
  .leftJoin(entityTwoAlias, eq(entityTwoAlias.id, entityRelations.entityTwo));

Perfect

11 replies

DTDrizzle Team

•Created by Martacus on 11/12/2023 in #help

Having trouble writing my sql statement in drizzle. Want to join two variables

Now to get them as names

11 replies

DTDrizzle Team

•Created by Martacus on 11/12/2023 in #help

Having trouble writing my sql statement in drizzle. Want to join two variables

That works 😄

11 replies

DTDrizzle Team

•Created by Martacus on 11/12/2023 in #help

Having trouble writing my sql statement in drizzle. Want to join two variables

Oh I see my mistake

11 replies

DTDrizzle Team

•Created by Martacus on 11/12/2023 in #help

Having trouble writing my sql statement in drizzle. Want to join two variables

Using the given query I still get 2 results, two times the same relation but with a different entity attached. I've tried doing:

  const entityOneAlias = alias(entities, 'entityOne');
  const entityTwoAlias = alias(entities, 'entityTwo');
  
  const data = await db.select().from(entityRelations).where(or(
    eq(entityRelations.entityOne, id),
    eq(entityRelations.entityTwo, id)
  ))
  .leftJoin(entities, eq(entityOneAlias.id, entityRelations.entityOne))
  .leftJoin(entities, eq(entityTwoAlias.id, entityRelations.entityTwo));

  const entityOneAlias = alias(entities, 'entityOne');
  const entityTwoAlias = alias(entities, 'entityTwo');
  
  const data = await db.select().from(entityRelations).where(or(
    eq(entityRelations.entityOne, id),
    eq(entityRelations.entityTwo, id)
  ))
  .leftJoin(entities, eq(entityOneAlias.id, entityRelations.entityOne))
  .leftJoin(entities, eq(entityTwoAlias.id, entityRelations.entityTwo));

But that still gives me Alias "entity" is already used in this query

11 replies

DTDrizzle Team

•Created by Martacus on 11/12/2023 in #help

Having trouble writing my sql statement in drizzle. Want to join two variables

Oh wait, is it because I am not adding aliases?

11 replies

DTDrizzle Team

•Created by Martacus on 11/12/2023 in #help

Having trouble writing my sql statement in drizzle. Want to join two variables

I've tried:

  const data = await db.select().from(entityRelations).where(or(
    eq(entityRelations.entityOne, id),
    eq(entityRelations.entityTwo, id)
  ))
  .leftJoin(entities, eq(entities.id, entityRelations.entityOne))
  .leftJoin(entities, eq(entities.id, entityRelations.entityTwo));

  const data = await db.select().from(entityRelations).where(or(
    eq(entityRelations.entityOne, id),
    eq(entityRelations.entityTwo, id)
  ))
  .leftJoin(entities, eq(entities.id, entityRelations.entityOne))
  .leftJoin(entities, eq(entities.id, entityRelations.entityTwo));

But i get a Alias "entity" is already used in this query

11 replies

DTDrizzle Team

•Created by Martacus on 11/12/2023 in #help

Having trouble writing my sql statement in drizzle. Want to join two variables

Yeah ofcourse:

export const entities = pgTable('entity', {
  id: varchar('id', { length: 64 }).primaryKey().$defaultFn(() => createId()),
  name: text("name").notNull(),
  description: text("description").notNull(),
  storyId: varchar('storyId', { length: 64 }).notNull().references(() => stories.id),
  userId: varchar('userId', { length: 64 }).notNull().references(() => users.id),
  createdAt: timestamp("created_at").notNull().defaultNow(), 
});

export const entityRelations = pgTable('relation', {
  id: varchar('id', { length: 64 }).primaryKey().$defaultFn(() => createId()),
  name: text("name").notNull(),
  storyId: varchar('storyId', { length: 64 }).notNull().references(() => stories.id),
  entityOne: varchar('entityOneId', { length: 64 }).notNull().references(() => entities.id),
  entityTwo: varchar('entityTwoId', { length: 64 }).notNull().references(() => entities.id),
  createdAt: timestamp("created_at").notNull().defaultNow()
});

export const relationEvents = pgTable('relation_event', {
  id: varchar('id', { length: 64 }).primaryKey().$defaultFn(() => createId()),
  name: text("name").notNull(),
  description: text("description").notNull(),
  interaction: text("interaction"),
  createdAt: timestamp("created_at").notNull().defaultNow()
});

export const entities = pgTable('entity', {
  id: varchar('id', { length: 64 }).primaryKey().$defaultFn(() => createId()),
  name: text("name").notNull(),
  description: text("description").notNull(),
  storyId: varchar('storyId', { length: 64 }).notNull().references(() => stories.id),
  userId: varchar('userId', { length: 64 }).notNull().references(() => users.id),
  createdAt: timestamp("created_at").notNull().defaultNow(), 
});

export const entityRelations = pgTable('relation', {
  id: varchar('id', { length: 64 }).primaryKey().$defaultFn(() => createId()),
  name: text("name").notNull(),
  storyId: varchar('storyId', { length: 64 }).notNull().references(() => stories.id),
  entityOne: varchar('entityOneId', { length: 64 }).notNull().references(() => entities.id),
  entityTwo: varchar('entityTwoId', { length: 64 }).notNull().references(() => entities.id),
  createdAt: timestamp("created_at").notNull().defaultNow()
});

export const relationEvents = pgTable('relation_event', {
  id: varchar('id', { length: 64 }).primaryKey().$defaultFn(() => createId()),
  name: text("name").notNull(),
  description: text("description").notNull(),
  interaction: text("interaction"),
  createdAt: timestamp("created_at").notNull().defaultNow()
});

11 replies

DTDrizzle Team

•Created by Martacus on 11/5/2023 in #help

Unable to read error message when inserting withdb.insert().values()

tyty

8 replies

DTDrizzle Team

•Created by Martacus on 11/5/2023 in #help

Unable to read error message when inserting withdb.insert().values()

In the schema I added .defaultNow() to them. Will updatedAt be updated on every edit to the row?

 
export const users = pgTable("user", {
  id: uuid('id').defaultRandom().primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull(),
  password: text("password").notNull(), 
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow()
});

 
export const users = pgTable("user", {
  id: uuid('id').defaultRandom().primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull(),
  password: text("password").notNull(), 
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow()
});

8 replies

DTDrizzle Team

•Created by Martacus on 11/5/2023 in #help

Unable to read error message when inserting withdb.insert().values()

Okay so my bad, I had wrong types for the createdAt and updatedAt. The error is very complicated and my eyes just couldnt really focus.

8 replies

DTDrizzle Team

•Created by Martacus on 11/5/2023 in #help

Unable to read error message when inserting withdb.insert().values()

8 replies

Gaming

Programming