Devmen. Elite ruby forces. Development on Ruby on Rails, iOS, android.

Let's continue to talk about using Thinking Sphinx with Postgresql. Imagine, I have a Hotel table with a price column, which I want to index and in the future to do a search by price. Let's define migration:

create_table :hotels do |t|   
  t.string :name
  t.decimal :price, :precision => 8, :scale => 2
  t.string :currency, :default => "USD"
end

And define model:

class Hotel < ActiveRecord::Base     
  define_index do       
    indexes name       
    has price     
  end   
end

But the problem is that price can be specified in different currencies. And I want to make searches in only one currency. So we need to add a table with the daily changes in exchange rates.

create_table :currency_rates do |t|
  t.date :rate_date
  t.decimal :usd, :precision => 8, :scale => 4, :default => 1
  t.decimal :eur, :precision => 8, :scale => 4
  t.decimal :rur, :precision => 8, :scale => 4
  ... all other needed currencies
  t.timestamps
end

In addition, we have to convert each price to the desired currency when sphinx will build index. Let's define price_to_usd function:

CREATE OR REPLACE FUNCTION price_to_usd(price float, currency varchar) RETURNS integer AS $$
 DECLARE
   rate float;
 BEGIN
   EXECUTE 'SELECT "' || lower(currency) || '" FROM currency_rates ORDER BY rate_date desc, created_at desc LIMIT 1'
   INTO rate;

   RETURN CAST(price*rate AS int);
 END;
 $$ LANGUAGE plpgsql;

Then we need to modify sphinx index:

class Hotel < ActiveRecord::Base     
  define_index do
    indexes name
    has "price_to_usd(hotels.price, hotels.currency)", :as => :price_usd, :type => :integer
    group_by "price_to_usd(hotels.price, hotels.currency)"
  end
end

Important point is that we need to manually add group by expression for such manual attributes. This is all. So let's try to search:

$ rake ts:reindex
$ rails c
>> Hotel.search("", {:with => {:price_usd => 100..300}}).total_entries => 1194

Read more...

I really like to use Sphinx especially with Thinking Sphinx for full text search. But in project I am currently working on, there was a problem when I tried to rebuild the sphinx index. Another important notice is that I use Sphinx with Postgresql. Let's suppose I have a model City with a string field name, in which defined index:

class City < ActiveRecord::Base
  define_index do
    indexes name
  end
end

When you rebuild sphinx index, an error occurs:

$ rake ts:index
...
indexing index 'city_core'...
ERROR: index 'city_core': sql_range_query: ERROR: integer out of range
(DSN=pgsql://postgres:***@localhost:5432/test_development). total 172 docs, 4682 bytes
total 498.371 sec, 9 bytes/sec, 0.34 docs/sec
skipping non-plain index 'city'...

After searching online, I discovered that this problem occurs not only for me. The reason is that the PK field by default treated as four-byte integer and if the key value is large enough overflow occurs while rebuild index. As often happens in open source projects, despite the fact that the error message appeared for a long time ago, Thinking Sphinx authors have not yet corrected it. To solve this problem, you need to rebuild the sphinx with option --enable-id64

$ cd sphinx_sources_path
$ ./configure --enable-id64 --with-pgsql
$ make
$ sudo make install

and patch thinking sphinx. This small patch adds a conversion of the PK integer field to eight-bytes bigint type. Read more...