pg_search nested associated_against

[Karl]

Is it possible to nest associated_against? Kind of like:

pg_search_scope :tasty_search, :associated_against => {
:cheeses => [:kind, :brand => [:name] ],
:cracker => :kind

thx!

-karl

[Grant Hutchins]

I'm not sure. I believe we wrote it to work that way, but I don't see test coverage in our specs, so caveat emptor.

The other approach would be to set up a :through association and use that directly, which definitely does work and is tested.

has_many :brands, :through => :cheeses

 pg_search_scope :tasty_search, :associated_against => {
   :cheeses => :kind,

   :cracker => :kind,

   :brands => :name

 }

--
Grant Hutchins

Pivotal Labs

gr...@pivotallabs.com

[Karl Baum]

Hi Grant, i tried the following notation:

  pg_search_scope :keyword_search, :against=>Space.columns.select {|column| [:string, :text].include? column.type  }.map(&:name),

    :associated_against => {·

    :property => [ {:submarket => [:name]}, :street_address, :city, :state, :zip_code, :country, :building_class]

  }

And I get a postgres error:

      PGError: ERROR:  column properties.{:submarket=>[:name]} does not exist

      LINE 1: ..."spaces"."id" AS id, array_to_string(my_array_agg("propertie...

The has_many :through seems to work properly though.

thx!

[Slotos]

I'm stuck with somewhat more "advanced" problem. I have to search
through data that is 3 joins away from the model I'm calling search
on.

class Article < ActiveRecord::Base
has_many :authors, :through => :authorships, :order =>
{ :authorships => :position }

include PgSearch

pg_search_scope :fuzzy,
:associated_against => {
:article_translations =>
[:content, :name, :long_description, :short_description, :lead],
:authors => {
:translations => {:last_name, :first_name}
}
},
:using => {
:tsearch => { :dictionary => "ru_uk_en" }
}
end

class Author < ActiveRecord::Base
has_many :translations
end

As you can see I've assumed that nested includes have default rails
syntax. However it doesn't work.

PS: From what was written before I understand that this will be a
feature request.
PPS: I'm currently updating my project to rails 3.1 to test nested
has_many :through. Will report my findings here.

[Grant Hutchins]

You might want to consider using multisearchable and pointing it to a method that does all this for you whenever your Article records are saved.

Something like this:

class Article < ActiveRecord::Base

  include PgSearch

  multisearchable :content, :title, :author_name

  def author_name

    # code that figures out author_name

  end

end

However, this technique will put things into a global search index, so any other models you add multisearchable to will get mixed into the results.

If you only want Article records, you could work around this by calling something like PgSearch::Document.where(:searchable_type => "Article").search("foo bar") 

Grant

[Slotos]

Wow, I haven't even thought about investigating multisearchable
support for non-db fields. Thank you for pointing this out.

I will still try nested hm:t, we need rails 3.1 due to various
reasons. And I don't think this feedback will hurt :)

____
Dmitrii

[Slotos]

Updating on nested hm:t associations. It works just like that, without
any need to modify anything.

class Article < ActiveRecord::Base
has_many :authorships, :order => :position, :dependent => :destroy

has_many :authors, :through => :authorships, :order =>

'"authorships".position'
has_many :author_translations, :through => :authors, :source
=> :translations

include PgSearch

pg_search_scope :fuzzy,
:associated_against => {

:translations =>

[:content, :name, :long_description, :short_description, :lead],

:author_translations => [:full_name]

},
:using => {
:tsearch => { :dictionary => "ru_uk_en" }
}
end

And that's all I had to do.

[Slotos]

Regarding indexing such search. Resulting sql uses string_agg (or
array_to_string(array_agg())) for data sources, rendering gin indexes
unusable.

[Grant Hutchins]

Yes, that's correct. The only way to index cross-model searches right now is to use multisearch.

I have some ideas about making this possible without multisearch, but they are still on the drawing board.

Grant

[Slotos]

The thing is multisearch can't use non-sql fields.

I'm currently struggling with multi-join ts searches using custom
code. I'll report here when I get result that both uses indexes
( postgres refuses to use ts indexes when filtering across different
tables in one query ) and is memory effective.

[Slotos]

If .join().on() would return ActiveRecord::Relation object this would
work. Quick and dirty code follows.

def self.fts(query)
dupe = scoped
author_translations =
dupe.reflect_on_association(:author_translations)
translations = dupe.reflect_on_association(:translations)
search_query =
"plainto_tsquery(#{ActiveRecord::Base.sanitize(query)})"
source = {
author_translations => [
%[coalesce("#{author_translations.table_name}".full_name, '')]
].map{|field| "to_tsvector('ru_uk_en', #{field})" },
translations => [
%[coalesce("#{translations.table_name}".name, '')],
%[coalesce("#{translations.table_name}".lead, '')],
%[coalesce("#{translations.table_name}".content, '')],
%[coalesce("#{translations.table_name}".long_description,
'')],
%[coalesce("#{translations.table_name}".short_description,
'')]
].map{|field| "to_tsvector('ru_uk_en', #{field})" }
}
rank_query = %{}
source.keys.each do |association|
join_filter = source[association].map{|vector| "(#{vector} @@
#{search_query})" }.join(' OR ')
dupe = dupe.joins(association.name.to_sym).on( %
("#{association.table_name}".#{association.foreign_key} =
"#{dupe.table_name}".id AND ( #{join_filter} ) ) )
end

dupe
end

Overall it seems that postgresql won't use indices for where clause on
multi-join query. A way to counter this is to use JOIN ON filtration
(slippery road). However at this point I meet Arel, which is awesome
but... unexpected. From what I've read it should be possible to get
ActiveRecord::Relation object from Arel::SelectManager.

Good thing about .join().on() chain is that you don't have to worry
about all the intermediate joins with nested hm:t relations, .on()
only applies to the very last one.

I'm switching to custom built ts_vector field with before_save/trigger
updates. Thankfully I'm facing almost a "write once, read often" usage
scenario.

[Samson Ootoowak]

It is unfortunate though that ':through method' can't be done for
belongs_to relationships. I currently have a [Account -> belongs_to ->
Customer -> has_one -> Address] association and I would like to search
address fields from the account model but currently have no means to
do so elegantly. For now I am searching via customer and using the
found ids to get the accounts but it is not ideal. Any idea of nested
association would be difficult to add?

[Grant Hutchins]

Hi Samson,

I believe that you can accomplish what you want by merging scopes together.

Account.joins(:customer).merge(Customer.search_address("123 Easy St"))

You might even be able to do this:

Account.joins(:customer => :address).merge(Address.search_address("123 Easy St"))

Let me know if it works for you. I just thought of it and it seems more powerful than :associated_against

Thanks!

Grant

[Samson Ootoowak]

Thanks for the response Grant. Sadly, it didn't work.

@accounts.joins(:customer).merge(@distributor.customers.search('street'))

Where @accounts was an ActiveRecord Relation, threw an error:
PGError: ERROR: table name "accounts" specified more than once

Have it working like so at the moment:
customers = @distributor.customers.search(params[:query])
@accounts = @accounts.where(:customer_id => customers.map(&:id))

So will keep it that way for now. Thanks for trying to help.

[pablorod...@gmail.com]

You save my DAY Slotos ... your approach enlighten me !!!  thanks