Ruby on Rails select alias in where condition using postgresql-CodePudding

Im trying to return the supplier where the suppliers name is most similar to the name supplied. I'm using rails 6, and postgresql. In postgres you theoretically can't use a column alias in a where condition but you can if you wrap the select in parens. So I have the following

query = %Q[ (id, name, (similarity(lower(name),lower('Johns Limited'))) as sim)]
Supplier.select(query).where("company_id = 3 and sim > 0.65").order("sim DESC").limit(1)

which generates

 SELECT  id, name, (similarity(lower(compressedname),lower('Johns Limited'))) as sim FROM "suppliers" WHERE (company_id = 3 and sim > 0.65) /* loading for inspect */ ORDER BY sim DESC LIMIT $1  [["LIMIT", 1]]

so its stripped the parens around the select giving me ActiveRecord::StatementInvalid (PG::UndefinedColumn: ERROR: column "sim" does not exist) LINE 1: ...as sim FROM "suppliers" WHERE (company_id = 3 and sim > 0.65...

if I put the function in the where clause i cant sort by the best similarity

Any ideas - much appreciated

CodePudding user response：

You can use subquery in from and Arel to achieve this

select_columns = %Q[(id, name, similarity(lower(name),lower('Johns Limited')) as sim)]

Supplier.select('subquery.*').from(
  Supplier.select(select_columns).where(company_id: 3)
).where(
  Arel::Table.new('subquery')[:sim].gt(0.65)
).order(sim: :desc)

CodePudding user response：

many thanks to stefanuk yurik for the solution.

sensitivity = 0.65
supplername = "John's Company"
select_columns = %Q[id, name, similarity(lower(name),'lower(#{suppliername.gsub("'","''"}')) as sim]

Supplier.select('subquery.*').from(
  Supplier.select(select_columns).where(company_id: 3)
).where(
  Arel::Table.new('subquery')[:sim].gt(#{sensitivity})
).order(sim: :desc)`