Geek asks and answers

How to quickly match a record to the beginning of a long line?

I have a table articles(: Rails Model, but I think the problem is more with SQL), which has a column name permalink. For example, some of my permalinks:

title-of-article
great-article
great-article-about-obama
obama-stuff-about-him

I want to combine a query like great-article-about-obama-random-stuffwith great-article-about-obama. Can this be done while avoiding killing?

Thanks everyone

ps: We use Rails 3 and Postgresql (or Sqlite has not yet decided for production)

EDIT

We can do something similar, but the main drawback is that we have to extract all the permalinks from table articles:

permalinks = ['title-of-article','great-article','great-article-about-obama','obama-stuff-about-him']
string_to_match = 'great-article-about-obama-random-stuf'
result = permalinks.inject('') do |matched,permalink|
  matched = (string_to_match.include? permalink and permalink.size > matched.size) ? permalink : matched
end
result => 'great-article-about-obama'

I would love to find a way to do this right in SQLthe obvious reason of performance.

+3
source share
1

(w/postgres: http://www.postgresql.org/docs/8.3/static/textsearch-dictionaries.html + http://tenderlovemaking.com/2009/10/17/full-text-search-on-heroku/ solr, indexTank) :

request = "chien-qui-aboie"
article = nil

while !article do
  article = Article.where("permalink like ?", request+"%").select(:id).first
  request.gsub!(/-[^-]*$/) unless article
end

chien-qui-aboie%, chien-qui%, chien%.

"chien_qui_mange", "chien_qui_mange", "chien qui aboie"

- , , , .

+2

More articles:


All Articles