Thinking Sphinx how to index the plusminus rank of Thumbs_up gem - Thinking Sphinx

The old Google Groups will be going away soon, but your browser is incompatible with the new version.
Message from discussion Thinking Sphinx how to index the plusminus rank of Thumbs_up gem
Received-SPF: pass (google.com: domain of pm_boun...@pm.mtasv.net designates 74.205.19.141 as permitted sender) client-ip=74.205.19.141;
DomainKey-Signature: a=rsa-sha1; c=nofws; q=dns; s=pm; d=freelancing-gods.com;
 b=Ujiy7zubcEt2eJCHGAju05cmvczeSoeoQBxJIbJqo7Ss5LtVHk7wJ+WtsjkqYWKWk85gpReYCVxB
   ZFgLAZXcvqplsvzgtC5p2HRGpXPxjlxcu8VPc66kCQT3FB6IstOo;
From: "Pat Allan" <p...@freelancing-gods.com>
To: <thinking-sphinx@googlegroups.com>
Date: Thu, 08 Nov 2012 19:29:58 -0500
Subject: Re: [ts] Thinking Sphinx how to index the plusminus rank of
 Thumbs_up gem
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <6e9b94b1-da29-4335-bff2-7f9907ba432b@googlegroups.com>
References: <b7795697-4c3f-4897-9581-974c7ff481f4@googlegroups.com> <35355dde-2336-4211-ae2d-4bff90043a84@googlegroups.com> <d5ffdf32-2d3f-4ce9-bed1-32709f2ba300@googlegroups.com> <E0CA9E95-C347-4CE7-B62B-C773A5DF4...@freelancing-gods.com> <36608d1c-27a4-4c32-b53a-e21b0278a204@googlegroups.com> <6e9b94b1-da29-4335-bff2-7f9907ba432b@googlegroups.com>
Message-ID: <3E1A344F-44E2-439C-BA3F-5182380CB...@freelancing-gods.com>

Great to hear :)=0A=0A-- =0APat=0A=0AOn 09/11/2012, at 7:27 AM, Mike C=2E=
 wrote:=0A=0A> Hey Pat=21 My apologies I didn=27t read you=27re last sent=
ence this morning stating the difference between MYSQL and Postgresql for=
 the query=2E I was using mysql and I was planning on switching over to p=
ostgres and now that I did It works perfect =3D) thx much=21=0A> =0A> On =
Thursday, November 8, 2012 9:53:52 AM UTC-5, Mike C=2E wrote:=0A> Thanks =
much for the response=21 You=27re 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 sti=
ll working fine for Most amount of ratings and Least amount of ratings=2E=
=0A> =0A> Although I haven=27t had luck yet with:=0A> =0A> has =22SUM(CAS=
E vote WHEN TRUE THEN 1=2E0 ELSE -1=2E0 END)=22, :as =3D> :plusminus, :ty=
pe =3D> :float=0A> =0A> It seems like it should work the way you have it,=
 although when I filter the results it doesn=27t show the top rated resul=
ts=2E=0A> =0A> I get something like this:=0A> =0A> Person1=09 Person2=09 =
Person3=09 Person4=0A> =0A> +5=09 +1=09 +2=09 +8=0A> =0A> Instead of:=0A>=
 =0A> Person4=09 Person1=09 Person3=09 Person2=0A> =0A> +8=09 +5=09 +2=09=
 +1=0A> =0A> (Think I used a different email for the response and it wasn=
=27t showing up on the board so reposting it here=2E)=0A> =0A> On Thursda=
y, November 8, 2012 4:13:39 AM UTC-5, Pat Allan wrote:=0A> The join call =
should be enough=2E=2E=2E shouldn=27t be any different to adding in the v=
ote_ids attribute as well=2E=0A> As for highest/lowest rating, this shoul=
d do the trick:=0A> =0A>   has =22SUM(CASE vote WHEN TRUE THEN 1=2E0 ELSE=
 -1=2E0 END)=22, :as =3D> :plusminus, :type =3D> :float=0A> =0A> It=27s i=
mportant to note that Sphinx integer attributes are unsigned, so you woul=
dn=27t get totals less than zero working properly - hence using floats in=
stead=2E If you=27re using MySQL, then you can use an IF function instead=
, or stick with the case change TRUE to 1=2E=0A> =0A> Anything else you=27=
re still stuck on? Sorry I=27ve been slow to respond, things have been fl=
at out here=2E=0A> =0A> -- =0A> Pat=0A> =0A> On 08/11/2012, at 1:04 PM, M=
ike C=2E wrote:=0A> =0A> > Well It appears that I got this one to work to=
day, after browsing the TS group and looking at more COUNT queries=2E I n=
eeded to add =22has votes(:id), :as =3D> :vote_ids) first:=0A> > =0A> > d=
efine_index do=0A> > =0A> >   has votes(:id), as: :vote_ids=0A> >   has =22=
COUNT(DISTINCT votes=2Eid)=22, as: :rating_count, type: :integer=0A> >   =
join votes=0A> > =0A> > end=0A> > =0A> > On Wednesday, November 7, 2012 2=
:19:09 PM UTC-5, Mike C=2E wrote:=0A> > Thumbs_up generates a votes model=
:=0A> > =0A> > class Vote < ActiveRecord::Base=0A> > =0A> >   scope :for_=
voter, lambda =7B =7C*args=7C where(=5B=22voter_id =3D ? AND voter_type =3D=
 ?=22, args=2Efirst=2Eid, args=2Efirst=2Eclass=2Ename=5D) =7D=0A> >   sco=
pe :for_voteable, lambda =7B =7C*args=7C where(=5B=22voteable_id =3D ? AN=
D voteable_type =3D ?=22, args=2Efirst=2Eid, args=2Efirst=2Eclass=2Ename=5D=
) =7D=0A> >   scope :recent, lambda =7B =7C*args=7C where(=5B=22created_a=
t > ?=22, (args=2Efirst =7C=7C 2=2Eweeks=2Eago)=5D) =7D=0A> >   scope :de=
scending, order(=22created_at DESC=22)=0A> > =0A> >   belongs_to :voteabl=
e, :polymorphic =3D> true=0A> >   belongs_to :voter, :polymorphic =3D> tr=
ue=0A> > =0A> >   attr_accessible :vote, :voter, :voteable=0A> > =0A> >  =
 =23 Comment out the line below to allow multiple votes per user=2E=0A> >=
   validates_uniqueness_of :voteable_id, :scope =3D> =5B:voteable_type, :=
voter_type, :voter_id=5D=0A> > =0A> > end=0A> > =0A> > and a vote migrati=
on containing:=0A> > =0A> > create_table =22votes=22, :force =3D> true do=
 =7Ct=7C=0A> >   t=2Eboolean  =22vote=22,          :default =3D> false=0A=
> >   t=2Einteger  =22voteable_id=22,                      :null =3D> fal=
se=0A> >   t=2Estring   =22voteable_type=22,                    :null =3D=
> false=0A> >   t=2Einteger  =22voter_id=22=0A> >   t=2Estring   =22voter=
_type=22=0A> >   t=2Edatetime =22created_at=22,                       :nu=
ll =3D> false=0A> >   t=2Edatetime =22updated_at=22,                     =
  :null =3D> false=0A> > end=0A> > =0A> > I added this to my person model=
 to filter by Most/Least amount of ratings:=0A> > =0A> > has_many :votes,=
 as: :voteable=0A> > =0A> > define_index do=0A> > =0A> >   has =22COUNT(v=
otes=2Eid)=22, as: :rating, type: :integer  =0A> >   join votes=0A> > =0A=
> > end=0A> > =0A> > Although it isn=27t filtering the results by the num=
ber of votes that person has correctly=2E=0A> > =0A> > The other issue is=
 figuring out how to index the Highest and Lowest Rating=2E (AKA plusminu=
s method in thumbs_up)=0A> > =0A> > plusminus =3D (votes_for - votes_agai=
nst)=0A> > =0A> > votes_for is total votes that are equal to 1 for voteab=
le_id and voteable_type=0A> > votes_against is total votes that are equal=
 to 0 for voteable_id and voteable_type=0A> > =0A> > Anyone able to make =
sense of this in sql for the attributes to index =3D)=0A> > =0A> > On Mon=
day, November 5, 2012 10:56:18 PM UTC-5, Mike C=2E wrote:=0A> > Anyone ha=
ve experience with indexing vote ranks with the Thumbs_up gem?=0A> > =0A>=
 > I=27m trying to figure out these 4 attributes:=0A> > =0A> > has Highes=
t Rating (the plusminus tally in gem) DESC=0A> > has Lowest Rating (the p=
lusminus tally in gem) ASC=0A> > has Most Ratings (total amount of votes)=
 DESC=0A> > has Least Ratings (total amount of votes) ASC=0A> > =0A> > --=
 =0A> > You received this message because you are subscribed to the Googl=
e Groups =22Thinking Sphinx=22 group=2E=0A> > To view this discussion on =
the web visit https://groups=2Egoogle=2Ecom/d/msg/thinking-sphinx/-/SDTFB=
QML9nQJ=2E=0A> > To post to this group, send email to thinkin=2E=2E=2E=40=
googlegroups=2Ecom=2E=0A> > To unsubscribe from this group, send email to=
 thinking-sphi=2E=2E=2E=40googlegroups=2Ecom=2E=0A> > For more options, v=
isit this group at http://groups=2Egoogle=2Ecom/group/thinking-sphinx?hl=3D=
en=2E=0A> =0A> =0A> =0A> =0A> =0A> =0A> =0A> =0A> =0A> =0A> -- =0A> You r=
eceived this message because you are subscribed to the Google Groups =22T=
hinking Sphinx=22 group=2E=0A> To view this discussion on the web visit h=
ttps://groups=2Egoogle=2Ecom/d/msg/thinking-sphinx/-/oX6u-2OOl8EJ=2E=0A> =
To post to this group, send email to thinking-sphinx=40googlegroups=2Ecom=
=2E=0A> To unsubscribe from this group, send email to thinking-sphinx+uns=
ubscribe=40googlegroups=2Ecom=2E=0A> For more options, visit this group a=
t http://groups=2Egoogle=2Ecom/group/thinking-sphinx?hl=3Den=2E=0A=0A
Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy
Account Options
