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].
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.
