Drizzle Team•14mo ago

Use column name in like operator

I'm trying to create a query with a join where I get all entries whose value starts with the value of the root table. e.g. Tags = { ..., tag: "some", ... } tagsWithNoteIds = [{ ..., tag: "some", ... }, { ..., tag: "some/string", ... }]

DB.with(tagsWithNoteIds)
  .select({
    tagId: Tags.id,
    tag: Tags.tag,
    noteIds: sql`json_agg(${tagsWithNoteIds.noteIds})`,
  })
  .from(Tags)
  .leftJoin(tagsWithNoteIds, or(eq(tagsWithNoteIds.tag, Tags.tag), like(tagsWithNoteIds.tag, `${Tags.tag}/%`)))
  .where(eq(Tags.authorId, query.authorId))
  .groupBy(Tags.id);

DB.with(tagsWithNoteIds)
  .select({
    tagId: Tags.id,
    tag: Tags.tag,
    noteIds: sql`json_agg(${tagsWithNoteIds.noteIds})`,
  })
  .from(Tags)
  .leftJoin(tagsWithNoteIds, or(eq(tagsWithNoteIds.tag, Tags.tag), like(tagsWithNoteIds.tag, `${Tags.tag}/%`)))
  .where(eq(Tags.authorId, query.authorId))
  .groupBy(Tags.id);

I'm not sure how the syntax should be to do this in the like operator. Is this even possible? When I debug the SQL that is created by this it uses params: [ "[object Object]/%", "d792d767-6662-4a56-8b93-bb6dac1a350d" ] for the params.. In the end it would still need a way to know, that the column name shouldn't be used as a raw string so maybe this is just not possible?

1 Reply

BlumeOP•14mo ago

I was able to solve it via

or(eq(tagsWithNoteIds.tag, Tags.tag), sql`${tagsWithNoteIds.tag} LIKE ${Tags.tag} || '%'`)

or(eq(tagsWithNoteIds.tag, Tags.tag), sql`${tagsWithNoteIds.tag} LIKE ${Tags.tag} || '%'`)

Not as pretty but does the job

Gaming

Programming

Use column name in like operator