Thinking Sphinx how to index the plusminus rank of Thumbs_up gem

[Mike C.]

Anyone have experience with indexing vote ranks with the Thumbs_up gem?

I trying to figure out these 4 attributes:

has Highest Rating (the plusminus tally in gem) DESC

has Lowest Rating (the plusminus tally in gem) ASC

has Most Ratings (total amount of votes) DESC

has Lesast Ratings (total amount of votes) ASC

[Mike C.]

Thumbs_up generates a votes model:

class Vote < ActiveRecord::Base

  scope :for_voter, lambda { |*args| where(["voter_id = ? AND voter_type = ?", args.first.id, args.first.class.name]) }

  scope :for_voteable, lambda { |*args| where(["voteable_id = ? AND voteable_type = ?", args.first.id, args.first.class.name]) }

  scope :recent, lambda { |*args| where(["created_at > ?", (args.first || 2.weeks.ago)]) }

  scope :descending, order("created_at DESC")

  belongs_to :voteable, :polymorphic => true

  belongs_to :voter, :polymorphic => true

  attr_accessible :vote, :voter, :voteable

  # Comment out the line below to allow multiple votes per user.

  validates_uniqueness_of :voteable_id, :scope => [:voteable_type, :voter_type, :voter_id]

end

and a vote migration containing:

create_table "votes", :force => true do |t|

  t.boolean  "vote",          :default => false

  t.integer  "voteable_id",                      :null => false

  t.string   "voteable_type",                    :null => false

  t.integer  "voter_id"

  t.string   "voter_type"

  t.datetime "created_at",                       :null => false

  t.datetime "updated_at",                       :null => false

end

I added this to my person model to filter by Most/Least amount of ratings:

has_many :votes, as: :voteable

define_index do

  has "COUNT(votes.id)", as: :rating, type: :integer  

  join votes

end

Although it isn't filtering the results by the number of votes that person has correctly.

The other issue is figuring out how to index the Highest and Lowest Rating. (AKA plusminus method in thumbs_up)

plusminus = (votes_for - votes_against)

votes_for is total votes that are equal to 1 for voteable_id and voteable_type

votes_against is total votes that are equal to 0 for voteable_id and voteable_type

Anyone able to make sense of this in sql for the attributes to index =)

On Monday, November 5, 2012 10:56:18 PM UTC-5, Mike C. wrote:

Anyone have experience with indexing vote ranks with the Thumbs_up gem?

I'm trying to figure out these 4 attributes:

has Highest Rating (the plusminus tally in gem) DESC

has Lowest Rating (the plusminus tally in gem) ASC

has Most Ratings (total amount of votes) DESC

has Least Ratings (total amount of votes) ASC

[Mike C.]

Well It appears that I got this one to work today, after browsing the TS group and looking at more COUNT queries. I needed to add "has votes(:id), :as => :vote_ids) first:

define_index do

  has votes(:id), as: :vote_ids

  has "COUNT(DISTINCT votes.id)", as: :rating_count, type: :integer

  join votes

end

[Pat Allan]

The join call should be enough... shouldn't be any different to adding in the vote_ids attribute as well.

As for highest/lowest rating, this should do the trick:

has "SUM(CASE vote WHEN TRUE THEN 1.0 ELSE -1.0 END)", :as => :plusminus, :type => :float

It's important to note that Sphinx integer attributes are unsigned, so you wouldn't get totals less than zero working properly - hence using floats instead. If you're using MySQL, then you can use an IF function instead, or stick with the case change TRUE to 1.

Anything else you're still stuck on? Sorry I've been slow to respond, things have been flat out here.

--
Pat

> --
> You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group.
> To view this discussion on the web visit https://groups.google.com/d/msg/thinking-sphinx/-/SDTFBQML9nQJ.
> To post to this group, send email to thinkin...@googlegroups.com.
> To unsubscribe from this group, send email to thinking-sphi...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en.

[Mike C.]

Thanks much for the response! You're right I removed the vote_ids attribute and also the has_many association since it seems the association is added with the acts_as_voteable call on the top of model and the votes are still working fine for Most amount of ratings and Least amount of ratings.

Although I haven't had luck yet with:

has "SUM(CASE vote WHEN TRUE THEN 1.0 ELSE -1.0 END)", :as => :plusminus, :type => :float

It seems like it should work the way you have it, although when I filter the results it doesn't show the top rated results.

I get something like this:

Person1 Person2 Person3 Person4

+5 +1 +2 +8

Instead of:

Person4 Person1 Person3 Person2

+8 +5 +2 +1

(Think I used a different email for the response and it wasn't showing up on the board so reposting it here.)

[Mike C.]

Hey Pat! My apologies I didn't read you're last sentence this morning stating the difference between MYSQL and Postgresql for the query. I was using mysql and I was planning on switching over to postgres and now that I did It works perfect =) thx much!

[Pat Allan]

Great to hear :)

--
Pat

> To view this discussion on the web visit https://groups.google.com/d/msg/thinking-sphinx/-/oX6u-2OOl8EJ.

[Mike C.]

Hey there Pat,

It's strange, when I first tested the Top Rated attribute it seemed to be working, but now when testing the application with more data it doesn't seem to be doing the Tally correct. Each item can have a true vote (+1) and a false vote (-1). In the database the votes(table), votes(column) has t or f for true and false. I'm trying to sort by Top Rated items for the user model. User can be rated with a unique +1 or -1 vote. When I search with :order => "plusminus DESC" or :order => "plusminus ASC" it is not showing correct.

In my user model I have this in the Index.

define index

  has "COUNT(DISTINCT votes.id)", :as => :rating_count, :type => :integer #MOST RATINGS/LEAST RATINGS FILTER (WORKS)

  has "SUM(CASE vote WHEN TRUE THEN 1.0 ELSE -1.0 END)", :as => :plusminus, :type => :float #TOP RATED/LEAST RATED FILTER (DOESN'T WORK)

  join votes

end

I need to get the (User's count of +1 votes) - (User's count of -1 votes)

In the results I am currently getting User out of order with +2, +1, +3, +1, +1 etc.. instead of +3, +2, +1 etc...

It doesn't appear that the SUM is working.

[Mike C.]

Also, when I filter :order => "plusminus ASC"

I added some down votes to 3 users. It did put those three users to the top of the results, the problem is that the order of those 3 results is wrong. It is showing up as the order I voted on them -1, -2, -2 (-1 being the last user I voted on)

[Pat Allan]

Hi Mike

Can you share with us your full index definition?

Cheers

--
Pat

On 07/12/2012, at 5:20 AM, Mike C. wrote:

> Also, when I filter :order => "plusminus ASC"
>
> I added some down votes to 3 users. It did put those three users to the top of the results, the problem is that the order of those 3 results is wrong. It is showing up as the order I voted on them -1, -2, -2 (-1 being the last user I voted on)
>

> --
> You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group.

> To view this discussion on the web visit https://groups.google.com/d/msg/thinking-sphinx/-/ZACphJWXKDIJ.

[Mike C.]

#PERSON MODEL

define_index do

  indexes name

  indexes tag_taggings.tag(:name), as: :tags

  has :id

  has view_count, as: :impressions

  has "COUNT(comments.id)", as: :reviews, type: :integer

  has "COUNT(DISTINCT votes.id)", as: :rating_count, type: :integer

  has "SUM(CASE votes.vote WHEN TRUE THEN 1.0 ELSE -1.0 END)", as: :rating, type: :float

  has "RADIANS(lat)", as: :lat, type: :float

  has "RADIANS(lng)", as: :lng, type: :float

  has created_at, updated_at

  group_by "lat", "lng"

  join comments

  join votes

end

#PERSON CONTROLLER

conditions = {}

%w(name tags).each do |i|

  i = i.to_sym

  next unless params[i]

  conditions[i] = params[i]

end

      

#SORT RATINGS

if params[:o] == "rate_desc"

  order_by = "rating DESC"

elsif params[:o] == "rate_asc"

  order_by = "rating ASC"

elsif params[:o] == "rate_count_desc"

  order_by = "rating_count DESC"

elsif params[:o] == "rate_count_asc"

  order_by = "rating_count ASC"

      

#SORT REVIEWS

elsif params[:o] == "reviews_desc"

  order_by = "reviews DESC"

elsif params[:o] == "reviews_asc"

  order_by = "reviews ASC"

#SORT VIEWS

elsif params[:o] == "views_desc"

  order_by = "impressions DESC"

elsif params[:o] == "views_asc"

  order_by = "impressions ASC"

#SORT TIME

elsif params[:o] == "day"

  filters = { :created_at => 1.day.ago..Time.now }

  order_by = "created_at DESC"

elsif params[:o] == "week"

  filters = { :created_at => 1.week.ago..Time.now }

  order_by = "created_at DESC"

elsif params[:o] == "month"

  filters = { :created_at => 1.month.ago..Time.now }

  order_by = "created_at DESC"

elsif params[:o] == "year"

  filters = { :created_at => 1.year.ago..Time.now }

  order_by = "created_at DESC"

else

  order_by = "created_at DESC"

end

#SEARCH IT!

p = Person.search(params[:q], :conditions => conditions, :order => order_by, :with => filters, :per_page => 9999999)

[Pat Allan]

What happens if you remove the comments and tag references? I think it may be a case of the other joins increasing the number of records, and thus vote counts are increased for some records.

If that's the case, then things will be tricky… I'd imagine you'd need to have an association each for positive and negative votes (add a condition on the vote column), and then use the count of distinct votes.id to figure out the value…

has_many :positive_votes, :class_name => 'Vote', :conditions => {:vote => true}
has_many :negative_votes, :class_name => 'Vote', :conditions => {:vote => false}

has "CAST(COUNT(DISTINCT positive_votes.id) - COUNT(DISTINCT negative_votes.id)) as float",
:as => :rating, :type => :float
join positive_votes
join negative_votes

I'm not entirely sure of that logic - and the join table aliases may be something other than positive_votes and negative_votes. In short: a bit of trial and error will be required.

Cheers

--
Pat

> To view this discussion on the web visit https://groups.google.com/d/msg/thinking-sphinx/-/ESfL1edDbL4J.

[Mike C.]

You're right! It appears the culprit is "indexes tag_taggings.tag(:name), as: :tags". When I remove this attribute from the index the ratings work perfect. Although I need the tags as part of the search...

I tried adding like you said:

has_many :positive_votes, :class_name => 'Vote', :foreign_key => 'voteable_id', :conditions => {:vote => true}

has_many :negative_votes, :class_name => 'Vote', :foreign_key => 'voteable_id', :conditions => {:vote => false}

define index

  has "CAST(COUNT(DISTINCT positive_votes.id) - COUNT(DISTINCT negative_votes.id)) as float", :as => :rating, :type => :float
  join positive_votes
  join negative_votes

end

but I keep getting an issue like:

OR

 

[Mike C.]

My bad...I deciphered the part you mentioned "and the join table aliases may be something other than positive_votes and negative_votes" by checking out the development.sphinx config file. They were labeled as positive_votes_people and negative_votes_people. So the define index became thus:

has "CAST(COUNT(DISTINCT positive_votes_people.id) - COUNT(DISTINCT negative_votes_people.id) as float)", :as => :rating, :type => :float
join positive_votes

join negative_votes

and Voila, it works perfect!

Thanks so much Pat, you are the best!

[Pat Allan]

Great to know it's working :)

> OR

>
>
>
>
>
> --
> You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group.

> To view this discussion on the web visit https://groups.google.com/d/msg/thinking-sphinx/-/0JjKshHjjoUJ.