如何将Ruby函数放入SQLite3查询中? [英] How do I put a Ruby function into a SQLite3 query?

问题描述

我有一个需要放入SQLite3查询的函数.

I have a function which I need to put into a SQLite3 query.

我有以下方法:

def levenshtein(a, b)
  case
    when a.empty? then b.length
    when b.empty? then a.length
    else [(a[0] == b[0] ? 0 : 1) + levenshtein(a[1..-1], b[1..-1]),
      1 + levenshtein(a[1..-1], b),
      1 + levenshtein(a, b[1..-1])].min
  end
end

我想做一个看起来像这样的查询:

and I want to do a query that looks something like this:

@results = The_db.where('levenshtein("name", ?) < 3', '#{userinput}')

我想在The_db中查找name值的地方，其中name列的值和用户输入之间的编辑距离小于3.问题是我不知道如何在查询中使用Ruby函数.这有可能吗?

What I want to find the values of name in The_db where the edit distance between the value of the name column and the user input is less than 3. The problem is that I don't know how to use a Ruby function in a query. Is this even possible?

推荐答案

看看

Have a look at the create_function method. You can use it to create a custom function like this (where you have already defined your levenshtein Ruby method):

db.create_function "levenshtein", 2 do |func, a, b|
  func.result = levenshtein(a, b)
end

然后您可以在SQL中使用它:

You can then use it in your SQL:

# first set up some data
db.execute 'create table names (name varchar(30))'
%w{Sam Ian Matt John Albert}.each do |n|
  db.execute 'insert into names values (?)', n
end

#Then use the custom function
puts db.execute('select * from names where levenshtein(name, ?) < 3', 'Jan')

在此示例中，输出为

Sam
Ian
John

我尚未在Rails中对此进行测试，但我认为它应该可以工作，查询字符串只是传递给数据库.您可以使用 . (我不知道如何配置Rails，以便所有ActiveRecord连接都定义了功能-如果在控制器中添加该功能似乎可以正常工作，但这并不理想.)

I haven’t tested this in Rails, but I think it should work, the query string is just passed through to the database. You can get the Sqlite db object using ActiveRecord::Base.connection.raw_connection. (I don’t know how to configure Rails so that all ActiveRecord connections have the function defined however – it does seem to work if you add the function in the controller, but that isn’t ideal).

已经显示了如何完成 ，但不确定是否应在网络应用中完成 .您可能不想在生产中使用Sqlite.如果您使用例如Postgres在生产中具有自己的levenshtein功能(如 Tin Man的答案中所建议)，但想在其中使用Sqlite开发.

Having shown how this can be done, I’m not sure if it should be done in a web app. You probably don’t want to use Sqlite in production. Perhaps it could be useful if you’re using e.g. Postgres with its own levenshtein function in production (as suggested in the Tin Man’s answer), but want to use Sqlite in development.

这篇关于如何将Ruby函数放入SQLite3查询中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！