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}

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)

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?

1 Reply

cHef.41000OP•2y ago

The generated sql looks like this when I only order by one calculated column.

select "id", "col1", "col2",  similarity( col1, $1), similarity( col2, $2 )
        from "my_schema"."my_view"
        where col1 % $3 OR col2 % $4
        order by similarity("my_view"."col1", $5) desc
        limit $6

select "id", "col1", "col2",  similarity( col1, $1), similarity( col2, $2 )
        from "my_schema"."my_view"
        where col1 % $3 OR col2 % $4
        order by similarity("my_view"."col1", $5) desc
        limit $6

Gaming

Programming

Order by multiple computed columns