jimmydirt Posts - Answer Overflow

jimmydirt

•Created by jimmydirt on 10/10/2023 in #help

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

21 replies

Gaming

Programming