Drizzle Team•13mo ago

Joining multiple instances of same table?

Suppose I have an items table where each item can have up to 3 tags. This is hardcoded - the items table has columns tag1id tag2id and tag3id. I want to find all items that have a given tag. The code below seems to select the proper items but I do not get the tag2 or tag3 properties on the result. Is there something I'm doing wrong, and more generally, is there a better way to achieve this?

let t1 = alias(tags, 't1');
let t2 = alias(tags, 't2');
let t3 = alias(tags, 't3');

let result = await db
  .select({
    ID: items.ID,
    title: items.title,
    tag1: t1.name,
    tag2: t2.name,
    tag3: t3.name
  })
  .from(items)
  .leftJoin(t1, eq(items.tag1ID, t1.ID))
  .leftJoin(t2, eq(items.tag2ID, t2.ID))
  .leftJoin(t3, eq(items.tag3ID, t3.ID))
  .where(
    and(
      eq(items.isDeleted, false), 
      or(
        eq(t1.name, TAG_NAME),
        eq(t2.name, TAG_NAME),
        eq(t3.name, TAG_NAME)
      )
    )
  );

let t1 = alias(tags, 't1');
let t2 = alias(tags, 't2');
let t3 = alias(tags, 't3');

let result = await db
  .select({
    ID: items.ID,
    title: items.title,
    tag1: t1.name,
    tag2: t2.name,
    tag3: t3.name
  })
  .from(items)
  .leftJoin(t1, eq(items.tag1ID, t1.ID))
  .leftJoin(t2, eq(items.tag2ID, t2.ID))
  .leftJoin(t3, eq(items.tag3ID, t3.ID))
  .where(
    and(
      eq(items.isDeleted, false), 
      or(
        eq(t1.name, TAG_NAME),
        eq(t2.name, TAG_NAME),
        eq(t3.name, TAG_NAME)
      )
    )
  );

6 Replies

Angelelz•13mo ago

You are doing it right, the problems is data aggregation You are probably getting an array of type:

{
  ID: WhateverTheIdTypeIs,
  title: string,
  tag1: string | null,
  tag2: string | null,
  tag3: string | null
}

{
  ID: WhateverTheIdTypeIs,
  title: string,
  tag1: string | null,
  tag2: string | null,
  tag3: string | null
}

But since you are left joining, whenever tag1 is string the other 2 tags will be null Have you tried using innerJoin(...) instead of leftJoin()?

jimmydirt•13mo ago

Trying that gives the same results. The following query yields the correct results. My guess is there is something going on with the aliasing.

  select i.id, i.title, t1.name as tag1, t2.name as tag2, t3.name as tag3
  from items i
  left join tags t1 on t1.id = p.tag1_id
  left join tags t2 on t2.id = p.tag2_id
  left join tags t3 on t3.id = p.tag3_id
  where i.is_deleted = false and (t1.name = 'test' or t2.name = 'test' or t3.name = 'test')

  select i.id, i.title, t1.name as tag1, t2.name as tag2, t3.name as tag3
  from items i
  left join tags t1 on t1.id = p.tag1_id
  left join tags t2 on t2.id = p.tag2_id
  left join tags t3 on t3.id = p.tag3_id
  where i.is_deleted = false and (t1.name = 'test' or t2.name = 'test' or t3.name = 'test')

The alias feature isn't well documented and doesn't seem to be used much, which makes me think I'm using a weird pattern for a fairly common thing. I hardcoded the 3 tag limit thinking it would make things easier or have performance benefits, but maybe a standard items_tags join table would make more sense even with that given constraint?

Angelelz•13mo ago

If you pass {logger: true} to the drizzle function during instantiation, you'll get your queries logged to the console. I'm pretty sure the printed query will be exactly what you'd expect That would rule out any issue with the alias If you had the relations defined you could use the Relational Query API and it would do the data aggregation for you

jimmydirt•13mo ago

Ok, using the logger, it seems that the column names are not being aliased (e.g. select ... t1.name, t2.name ...) so only the last one returns. I guess I might have expected them to be aliased with the property names passed to the select function (e.g. select ... t1.name as tag1), but it appears that mapping happens later. Is there any way to specify that behavior? edit - I was able get it working with sql`${t1.name} as abc` - is this recommended or is there a better way? I have the relations defined and I tried that way first but I was unsure how to reference the aliased tables in the where clause:

  export const itemsRelations = relations(items, ({ one }) => ({
  tag1: one(tags, {
    fields: [items.tag1ID],
    references: [tags.ID]
  }),
  tag2: one(tags, {
    fields: [items.tag2ID],
    references: [tags.ID]
  }),
  tag3: one(tags, {
    fields: [items.tag3ID],
    references: [tags.ID]
  })
}));

  export const itemsRelations = relations(items, ({ one }) => ({
  tag1: one(tags, {
    fields: [items.tag1ID],
    references: [tags.ID]
  }),
  tag2: one(tags, {
    fields: [items.tag2ID],
    references: [tags.ID]
  }),
  tag3: one(tags, {
    fields: [items.tag3ID],
    references: [tags.ID]
  })
}));

let result = await db.query.items.findMany({
    // how to add "or(eq(tag1.name, TAG_NAME), eq(tag2.name, TAG_NAME), eq(tag3.name, TAG_NAME))" ?
    where: eq(items.isDeleted, false),
    with: { 
      tag1: { columns: { name: true } }, 
      tag2: { columns: { name: true } }, 
      tag3: { columns: { name: true } }
    }
  });

let result = await db.query.items.findMany({
    // how to add "or(eq(tag1.name, TAG_NAME), eq(tag2.name, TAG_NAME), eq(tag3.name, TAG_NAME))" ?
    where: eq(items.isDeleted, false),
    with: { 
      tag1: { columns: { name: true } }, 
      tag2: { columns: { name: true } }, 
      tag3: { columns: { name: true } }
    }
  });

Angelelz•13mo ago

Is there any way to specify that behavior?

One way is how you did it and another is

sql`${t1.name}`.as("tag1")

sql`${t1.name}`.as("tag1")

See if this works for the RQB:

let result = await db.query.items.findMany({
    // how to add "or(eq(tag1.id, TAG_NAME), eq(tag2.id, TAG_NAME), eq(tag3.id, TAG_NAME))" ?
    where: eq(items.isDeleted, false),
    with: { 
      tag1: {
        columns: { name: true },
        where: (tag1) => eq(tag1.name, TAG_NAME)
      }, 
      tag2: {
        columns: { name: true },
        where: (tag2) => eq(tag2.name, TAG_NAME)
      }, 
      tag3: {
        columns: { name: true },
        where: (tag3) => eq(tag3.name, TAG_NAME)
      }
    }
  });

let result = await db.query.items.findMany({
    // how to add "or(eq(tag1.id, TAG_NAME), eq(tag2.id, TAG_NAME), eq(tag3.id, TAG_NAME))" ?
    where: eq(items.isDeleted, false),
    with: { 
      tag1: {
        columns: { name: true },
        where: (tag1) => eq(tag1.name, TAG_NAME)
      }, 
      tag2: {
        columns: { name: true },
        where: (tag2) => eq(tag2.name, TAG_NAME)
      }, 
      tag3: {
        columns: { name: true },
        where: (tag3) => eq(tag3.name, TAG_NAME)
      }
    }
  });

jimmydirt•13mo ago

With that, it selects all items but only adds the tag data when it matches. So if tag2.name == 'test', then tag1 and tag3 will be null in the results even if they have values. I think this is the issue I was running into - the tag1 or tag2 or tag3 logic needs to be applied "at the top level," which seems outside the scope of the RQB? What do you think of the idea to switch it to a many-to-many join table (items_tags)? That should make this query fit better with the RQB, which I would like to use wherever possible. It's just adding another table which seems like it would be less performant but that might not be a necessary optimization right now. Either way I do at least have a workable solution for now and I thank you for your time helping me with this! One other idea would be to use the tag name as the primary key and avoid any joins, I'm just worried that could create problems down the road

Gaming

Programming

Joining multiple instances of same table?