CODEWITHSUNDEEP
mysqlruby-on-railsthinking-sphinx
Pioz
Pioz

Reputation: 6321

How to do a query with Thinking Sphinx and MySQL

I have the following models on my Rails app:

class User < ActiveRecord::Base
  has_many :tags
  has_many :talents, :through => :tags
  has_many :ratings

  define_index do
    indexes [:first_name, :last_name], :as => :name, :type => :string
    has 'AVG(ratings.value)', :as => :ratings, :type => :integer
    has tags(:talent_id), :as => :talent_id
    set_property :delta => true
  end

  def self.ts_search(q, talent)
    User.search q, :with => {:talent_id => talent.id}, :order => 'ratings DESC', :sort_mode => :extended
  end
end

class Talent < ActiveRecord::Base
  has_many :users, :through => :tags
end

class Rating < ActiveRecord::Base
  belongs_to :user
  belongs_to :talent
end

Now I need to retrieve with Thinking Sphinx 2.0.14 all users that have a specific talent and sort them by the average value of the ratings of that specific talent.

With the function def ts_search(q, talent) I retrieve all users with a specific talent and sort them by the average value of the ratings of all talents.

How I can modify this function to sort users by the average value of the ratings of that specific talent?

Thanks to all!

Upvotes: 0

Views: 185

Answers (2)

Ganesh Shrivas
Ganesh Shrivas

Reputation: 157

You can make 'OR' and 'And' query in following ways: Assume you want to search with Or condition query_arr = ["test", "test-xyz"]

Your search query like: User.search query_arr.join("( | )") So your query like "( test )|( test-xyz )" and it will search all user your text match with test or test-xyz

You can make "And" query by just replacing | to &.

Upvotes: 0

pat
pat

Reputation: 16226

I'm afraid what you're trying to do is not possible with Sphinx - it has no concept of associated records, hashes/dictionaries or joins, so there's no way to distinguish which rating links to which talent within the scope of each user.

Upvotes: 1

Related Questions