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.
On Thursday, November 8, 2012 7:30:45 PM UTC-5, Pat Allan wrote:
>
> Great to hear :)
>
> --
> Pat
>
> On 09/11/2012, at 7:27 AM, Mike C. wrote:
>
> > 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!
> >
> > On Thursday, November 8, 2012 9:53:52 AM UTC-5, Mike C. wrote:
> > 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.)
> >
> > On Thursday, November 8, 2012 4:13:39 AM UTC-5, Pat Allan wrote:
> > 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
> >
> > On 08/11/2012, at 1:04 PM, Mike C. wrote:
> >
> > > 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
> > >
> > > On Wednesday, November 7, 2012 2:19:09 PM UTC-5, Mike C. wrote:
> > > 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
> > >
> > > --
> > > 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 [email protected].
> > > To unsubscribe from this group, send email to
> [email protected].
> > > For more options, visit this group at
> http://groups.google.com/group/thinking-sphinx?hl=en.
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > --
> > 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/-/oX6u-2OOl8EJ.
> > To post to this group, send email to
> > [email protected]<javascript:>
> .
> > To unsubscribe from this group, send email to
> [email protected] <javascript:>.
> > For more options, visit this group at
> http://groups.google.com/group/thinking-sphinx?hl=en.
>
>
>
>
--
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/-/MSEKrjoa-4YJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/thinking-sphinx?hl=en.
