Joining multiple instances of same table?

Suppose I have an

items

items

table where each item can have up to 3

tags

tags

. This is hardcoded - the items table has columns

tag1id

tag1id

tag1id

tag1id

tag2id

tag2id

tag2id

tag2id and

tag3id

tag3id

tag3id

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

tag2

tag2

tag2 or

tag3

tag3

tag3

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

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

Angelelz•10/11/23, 12:09 AM

You are doing it right, the problems is data aggregation

Angelelz•10/11/23, 12:11 AM

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
}

Angelelz•10/11/23, 12:11 AM

But since you are left joining, whenever tag1 is string the other 2 tags will be null

Angelelz•10/11/23, 12:14 AM

Have you tried using innerJoin(...)innerJoin(...) instead of leftJoin()leftJoin()?

jimmydirtOP•10/11/23, 2:02 AM

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

jimmydirtOP•10/11/23, 2:03 AM

  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')

jimmydirtOP•10/11/23, 2:06 AM

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_tagsitems_tags join table would make more sense even with that given constraint?

Angelelz•10/11/23, 2:32 AM

If you pass {logger: true}{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

Angelelz•10/11/23, 2:32 AM

That would rule out any issue with the alias

Angelelz•10/11/23, 2:33 AM

If you had the relations defined you could use the Relational Query API and it would do the data aggregation for you

jimmydirtOP•10/11/23, 4:13 AM

Ok, using the logger, it seems that the column names are not being aliased (e.g. select ... t1.name, t2.name ...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 tag1select ... 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`sql`${t1.name} as abc`

is this recommended or is there a better way?

jimmydirtOP•10/11/23, 4:22 AM

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:

jimmydirtOP•10/11/23, 4:22 AM

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

jimmydirtOP•10/11/23, 4:22 AM

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•10/11/23, 12:07 PM

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")

Angelelz•10/11/23, 12:12 PM

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

jimmydirtOP•10/11/23, 5:21 PM

With that, it selects all items but only adds the tag data when it matches. So if tag2.name == 'test'tag2.name == 'test', then tag1tag1 and

tag3

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 tag3tag1 or tag2 or tag3 logic needs to be applied "at the top level," which seems outside the scope of the RQB?

jimmydirtOP•10/11/23, 5:29 PM

What do you think of the idea to switch it to a many-to-many join table (items_tagsitems_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.

jimmydirtOP•10/11/23, 5:30 PM

Either way I do at least have a workable solution for now and I thank you for your time helping me with this!

jimmydirtOP•10/11/23, 5:46 PM

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