DT
Drizzle Team•2y ago
Martacus

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
MAST•2y 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.
Martacus
MartacusOP•2y 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
MAST•2y 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.
Martacus
MartacusOP•2y 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

Did you find this page helpful?