Nested sub-query like searching in SunSpot Solr

[Kandada Boggu]

I am trying to implement a Solr based search for a message thread.
Each message can have many replies(The replies can be ONLY one level
deep.). I want to retrieve the parent messages with content matching
the search key OR replies matching the search key.

E.g:

Hello Jack
Hello Janice
How are you?
..

I am Janice
How are you?

Welcome to the Jungle
Nothing better to do.



Searching for `Janice` should return the following resultset:

Hello Jack # one of the child messages matches the key word
I am Janice # parent message matched the keyword)

My model is as follows:

class Message < ActiveRecord::Base
belongs_to :parent, :class_name => "Message"
has_many :replies, :class_name => "Message", :foreign_key
=> :parent_id
# content
searchable do
text :content
integer :parent_id
end
end

What is the DSL syntax for specifying nested subquery like conditions?

[Mat Brown]

Howdy,

Since Solr is nonrelational, nested subqueries of the type you'd find
in MySQL etc. aren't a possibility. Instead, you'll want to solve the
problem at index-time:

class Mesage
after_save do |message|
if message.parent.nil? then message.solr_index
else message.parent.solr_index
end
end

searchable :auto_index => false do
text :content
text :reply_content do
replies.map { |reply| reply.content }
end
end
end

Hope that clears things up!

Mat

> --
> You received this message because you are subscribed to the Google Groups "Sunspot" group.
> To post to this group, send email to ruby-s...@googlegroups.com.
> To unsubscribe from this group, send email to ruby-sunspot...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/ruby-sunspot?hl=en.
>
>

[Kandada Boggu]

Matt,
I simplified the example for the sake of clarity. I did not consider
this method as the message matching has addition criteria.

Each message has field called category_id. The search should return
the messages matching the text and the category_id.

class Message < ActiveRecord::Base
belongs_to :category

belongs_to :parent, :class_name => "Message"
has_many :replies, :class_name => "Message", :foreign_key
=> :parent_id
# content
searchable do
text :content

integer :parent_id, :category_id
end
end

My search should return the parent messages
- matching the category and the search text OR
- with any replies matching the category and the search text

-hks

[Nick Zadrozny]

Aha, looks like we're also having this conversation over on Stack Overflow, too ;-)

http://stackoverflow.com/questions/4099584/nested-searching-in-sunspot-solr/4120706

Basically, given your last update, I'm thinking you just search as simply as possible and use your ActiveRecord associations to grab the parents.

@search = Message.search do

  keywords params[:q]

  with(:category_id, params[:category_id])

end

@messages = @search.results

Then you can grab your parents by checking the "parent" association. A few extra SQL queries will help keep things simple without too much extra weight.

@parents = @messages.collect { |message| message.parent }.compact

That's an N+1 query, but if it's just a page of 10 results, I personally wouldn't be too bothered. New Relic is your friend here ;)

-- 

Nick Zadrozny

http://websolr.com — hassle-free hosted search