Drizzle TeamDT
Drizzle Team•3y ago
cHef.41000

Order by multiple computed columns

Hey y'all I'm trying to accomplish a query similar this query using Postgres’ text similarity function.
SELECT *,  similarity( col1, ${val1 || “”} ) as col1_sml, similarity( col2, ${val2 || “”} ) as col2_sml
        FROM my_schema.my_view
        WHERE col1 % ${val1 || “”} OR col2 % ${ val2 || “”}
        ORDER BY col1_sml DESC, col2_sml DESC
        LIMIT ${n}

I’ve tried using drizzle’s query builder like so: 
Import { my_view } from ‘../db/schema.ts’

db.select({
    Id: my_view.id
    col1: my_view.col1,
    col2: my_view.col2,
    col1_sml: sql`similarity( col1, ${val1 || “”} )` ,
    col2_sml: sql`similarity( col1, ${val1 || “”} )` ,
    })
      .from(my_view)
      .where(sql`${my_view.col1} % ${val1 || “”} OR ${my_view.col2} % ${ val2 || “”}`)
      .orderBy( ({ col1_sml }) => desc(col1_sml), ({col2_sml}) => desc(col2_sml) )
      .limit(n)


But I’m running into a type error on my orderBy clause saying my_view.col1 is of type any and thus not assignable to orderBy(). If I remove the second order by argument then everything works fine, but I need both. Additionally when I use toSQL() on this query, I notice that in the select values there are no ‘as’ keywords inserted to alias my similarity columns and thus in the orderBy clause it is re-running similarity(col, val) function which would really slow down the query.

Any help on how to accomplish this query using the query builder or should I just do a db.execute() on the above SQL statement?
Was this page helpful?