How to include a custom SQL function

Question

I'm using find_by_sql to execute an SQL query. I would like to be able to use Soundex and Levenshtein, but in order to use Levenshtein I need to include the function as a file.

This is my code so far:

info = params[:email].split('@')
name = info[0]
domain = info[1]

levenshtein = File.open("./lib/assets/mysql-function-levenshtein.sql")

results = Domain.find_by_sql(
    "" + levenshtein + "
    SELECT *
    FROM domains
    WHERE domain = '" + domain + "'"
)

I have no idea if simply including it in the query is even valid.

What's the best implementation?

By the way the file I'm trying to include is this: https://github.com/vyper/levenshtein-sql

Have you tried running that? I'd probably use File.read instead of File.open, or wrap the find_by_sql call in a File.open do ... end block. Otherwise I'm not overly fond of using custom SQL queries. Couldn't you just define the function on the database, and do a where("levensthtein('leonardo', 'leonardu')")? There's no real need to define the function every time you run the query, right? — Frost
– Frost, Commented Jul 16, 2012 at 8:42

Frost · Accepted Answer · 2012-07-16 10:44:41Z

6

First off, I think you'd be better off just defining that function in your database with a migration, so you wouldn't have to define it again for every query where you want to use it:

class AddLevenShteinFunctionToDatabase < ActiveRecord::Migration
  def up
    levenshtein = File.read("/path/to/levenshtein.sql")
    execute levenshtein
  end

  def down
    # maybe put some code here to delete the function
  end
end

With this done, you could also add a scope to your Domain model for doing these kind of queries:

scope :levenshtein, lambda {|s1, s2| select("levenshein(#{s1}, #{s2})") }

With this, you should be able to write your queries something like this:

results = Domain.levenshtein("LEONARDO", "LEONARDU").where(:domain => domain)

edited Jul 16, 2012 at 10:44

answered Jul 16, 2012 at 9:13

Frost

12k3 gold badges41 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Mythical Fish Over a year ago

Thanks for your answer, however I'm worried that using Active Record will have an impact on performance. There will be many requests per second, and I've been told that executing raw SQL will prove much faster.

Frost Over a year ago

In that case, I would recommend that you write a benchmark test for checking whether that is indeed the case, and if so, just use Domain.find_by_sql("select levenshtein('#{s1}', '#{s2}') from domains where domain = '#{domain}') instead.

Mythical Fish Over a year ago

Thanks, am I able to use this stored function with find_by_sql?

Frost Over a year ago

That's my assumption. If not, my answer would be pretty useless.

Mythical Fish Over a year ago

I'm receiving this error when running the migration: undefined local variable or method 'levenshtein' for #<AddLevenshteinFunctionToDatabase:0xb70b1d34>

|

Collectives™ on Stack Overflow

How to include a custom SQL function

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related