Multiple FROM support (word_similarity) fuzzy search

I'm trying to create this SQL:

SELECT *, similarity
FROM "app-user", word_similarity('test query', "fullName" || "email") as similarity
WHERE similarity > 0.3
ORDER BY similarity DESC

SELECT *, similarity
FROM "app-user", word_similarity('test query', "fullName" || "email") as similarity
WHERE similarity > 0.3
ORDER BY similarity DESC

Here's what I have, but Drizzle doesn't like the sql${user}:

Your "id" field references a column "app-user"."id", but the table "app-user" is not part of the query! Did you forget to join it?

const columnQuery = [user.fullName, user.email]
    .map((column: string) => `${column}`)
    .join(' || ')

  const rows = await db
    .select({ ...userReturnSensitiveColumns, similarity: true })
    .from(sql`${user}, word_similarity(${searchQuery}, ${columnQuery}) as similarity`)
    .where(
      searchQuery ? sql`similarity > 0.3` : undefined)
    )
    .orderBy(
      searchQuery ? desc('similarity') : undefined
    )

const columnQuery = [user.fullName, user.email]
    .map((column: string) => `${column}`)
    .join(' || ')

  const rows = await db
    .select({ ...userReturnSensitiveColumns, similarity: true })
    .from(sql`${user}, word_similarity(${searchQuery}, ${columnQuery}) as similarity`)
    .where(
      searchQuery ? sql`similarity > 0.3` : undefined)
    )
    .orderBy(
      searchQuery ? desc('similarity') : undefined
    )

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

Multiple FROM support (word_similarity) fuzzy search