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

I am trying to execute this sql statement with drizzle

SELECT er.*, e1.name AS entity_one_name, e2.name AS entity_two_name
FROM "public"."relation" er
JOIN "public"."entity" e1 ON er."entityOneId" = e1.id
JOIN "public"."entity" e2 ON er."entityTwoId" = e2.id;

SELECT er.*, e1.name AS entity_one_name, e2.name AS entity_two_name
FROM "public"."relation" er
JOIN "public"."entity" e1 ON er."entityOneId" = e1.id
JOIN "public"."entity" e2 ON er."entityTwoId" = e2.id;

But I am having trouble translating this to code. I am currently doing something like this:

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

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

But it's giving me the object two times with only one entity attached. Maybe my sql knowledge isnt up to date or im missing something in drizzle. Are there some examples i could look at?

4 Replies

MAST•14mo ago

Can you share the table definitions? In your SQL I see that you are selecting from relation table and joining on the entity table twice. Can't see the same thing in the drizzle translation.

MartacusOP•14mo ago

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()
});

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 Oh wait, is it because I am not adding aliases?

MAST•14mo ago

Not sure but can you try this:

db.select().from(entityRelations).leftJoin(entities, or(eq(entities.id, 'entityOneId'), eq(entities.id, 'entityTwoId')));

db.select().from(entityRelations).leftJoin(entities, or(eq(entities.id, 'entityOneId'), eq(entities.id, 'entityTwoId')));

If you want to join the same table twice you can use an alias and instead of the or you can do two leftJoins.

Joins [SQL] - Drizzle ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

MartacusOP•14mo ago

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 Oh I see my mistake That works 😄 Now to get them as names

  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

Gaming

Programming

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