jimmydirt
jimmydirt
PostsComments
DTDrizzle Team
Created by jimmydirt on 10/10/2023 in #help
Joining multiple instances of same table?
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
21 replies
DTDrizzle Team
Created by jimmydirt on 10/10/2023 in #help
Joining multiple instances of same table?
Either way I do at least have a workable solution for now and I thank you for your time helping me with this!
21 replies
DTDrizzle Team
Created by jimmydirt on 10/10/2023 in #help
Joining multiple instances of same table?
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.
21 replies
DTDrizzle Team
Created by jimmydirt on 10/10/2023 in #help
Joining multiple instances of same table?
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?
21 replies
DTDrizzle Team
Created by jimmydirt on 10/10/2023 in #help
Joining multiple instances of same table?
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 } }
    }
  });
21 replies
DTDrizzle Team
Created by jimmydirt on 10/10/2023 in #help
Joining multiple instances of same table?
  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]
  })
}));
21 replies
DTDrizzle Team
Created by jimmydirt on 10/10/2023 in #help
Joining multiple instances of same table?
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:
21 replies
DTDrizzle Team
Created by jimmydirt on 10/10/2023 in #help
Joining multiple instances of same table?
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?
21 replies
DTDrizzle Team
Created by jimmydirt on 10/10/2023 in #help
Joining multiple instances of same table?
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?
21 replies
DTDrizzle Team
Created by jimmydirt on 10/10/2023 in #help
Joining multiple instances of same table?
  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')
21 replies
DTDrizzle Team
Created by jimmydirt on 10/10/2023 in #help
Joining multiple instances of same table?
Trying that gives the same results. The following query yields the correct results. My guess is there is something going on with the aliasing.
21 replies