Drizzle Team•12mo ago

SELECT DISTINCT error in Drizzle query but not in raw query

Hi everyone, I'm getting a Postgres Error: SELECT DISTINCT, ORDER BY expressions must appear in select list, when Drizzle is executing a particular query. However, when I copy and paste the raw query generated by Drizzle and substitute in the params, I don't get any error and the query executes successfully. Here's the raw query that works:

SELECT DISTINCT
    "sessions"."id",
    "sessions"."timestamp",
    "vendors"."id",
    "vendors"."name",
    "vendors"."logo_url",
    similarity ("vendors"."name", 'charge')
FROM
    "app"."sessions"
    INNER JOIN "vendors"."vendors" ON "sessions"."vendor_id" = "vendors"."id"
WHERE
    similarity ("vendors"."name", 'charge') >= 0.2
ORDER BY
    (similarity ("vendors"."name", 'charge')) DESC

And here is the Drizzle code that is generating this query and the error:

  const results =  await db
    .selectDistinct({
      id: sessions.id,
      timestamp: sessions.timestamp,
      vendor: {
        id: vendors.id,
        name: vendors.name,
        logoUrl: vendors.logoUrl,
      },
      relevance: searchFn,
    })
    .from(sessions)
    .innerJoin(vendors, eq(sessions.vendorId, vendors.id))
    .where(gte(searchFn, 0.2))
    .orderBy(desc(searchFn))

Would appreciate any tips. Thanks!

4 Replies

Mykhailo•12mo ago

Hello, @terryball. Can you please provide your searchFn?

terryballOP•12mo ago

Yes:
const searchFn = sqlsimilarity(${vendors.name}, ${query})

Mykhailo•12mo ago

When you use a parameterized query with parameters such as ${query}, PostgreSQL internally processes these parameters and may treat each instance of the parameter as a separate expression, even if they are identical. Try this code

const query = 'name';
const searchFn = sql<number>`similarity(${vendors.name}, ${query})`;

await db
    .selectDistinct({
      id: sessions.id,
      timestamp: sessions.timestamp,
      vendor: {
        id: vendors.id,
        name: vendors.name,
        logoUrl: vendors.logoUrl,
      },
      relevance: sql<number>`${searchFn}`.as('relevance'), // add as relevance
    })
    .from(sessions)
    .innerJoin(vendors, eq(sessions.vendorId, vendors.id))
    .where(gte(searchFn, 0.2))
    .orderBy(({ relevance }) => desc(relevance)); // rewrite orderBy a bit

const query = 'name';
const searchFn = sql<number>`similarity(${vendors.name}, ${query})`;

await db
    .selectDistinct({
      id: sessions.id,
      timestamp: sessions.timestamp,
      vendor: {
        id: vendors.id,
        name: vendors.name,
        logoUrl: vendors.logoUrl,
      },
      relevance: sql<number>`${searchFn}`.as('relevance'), // add as relevance
    })
    .from(sessions)
    .innerJoin(vendors, eq(sessions.vendorId, vendors.id))
    .where(gte(searchFn, 0.2))
    .orderBy(({ relevance }) => desc(relevance)); // rewrite orderBy a bit

terryballOP•12mo ago

Got it, thank you so much @solo I'll give that a shot

Gaming

Programming

SELECT DISTINCT error in Drizzle query but not in raw query