DT
Drizzle Team•15mo ago
Blume

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
Blume
BlumeOP•15mo 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

Did you find this page helpful?