Hi Cary Try: join descendants
Run rake ts:conf, check what the table is aliased as, then use that in your attribute SQL snippet (in this case, presuming like you that it's descendants): has "COUNT(descendants.id)", :as => :descendants_count, :type => :integer Cheers -- Pat On 18/02/2011, at 8:50 AM, Cary FitzHugh wrote: > I've learned a bit more about sphinx and how it seems that it all boils down > to a single SQL query. > > I'd like to add this: > > has "(count(descendants.id))", :as=>:decendant_count, :type=>:integer > > and at the end of the define_index add: > > join "LEFT JOIN nodes decendants ON nodes.uid = descendants.source_uid" > > > But I'm thwarted by some error in THinkingSphinx::Join when I try to do > this... > > The correct SQL is *so* close... Any ideas? I'm looking into how I could > monkey-patch it to get this in there.... > > Any ideas? > > Thanks! > Cary > > > On Thu, Feb 17, 2011 at 4:03 PM, CFitzhugh <[email protected]> wrote: > Hi all, > > I have a DB with records which look something like this: > > Node > uid: <.....> > source_uid: <....> > ... > > A root node has a null source_uid, > child nodes will have the source uid set with the uid of a root node. > > Anyway - in rails I have my fancy has_many relationship: > > class Node > > has_many :descendants, :class_name=>"Node", :foreign_key=>'source_uid', > :primary_key=>'uid' > end > > In Sphinx the attribute I want to add is 'descendant_count'. > > To be able so sort a query result based on how many descendants the > node has. > > Is there any way to do this? > I have tried: > > has descendants.count > has descendants(:count) > has descendants > .... > > Nothing seems to generate valid SQL - so ts:rebuild ends up throwing > out exceptions on me. > > What I want to do is basically do a sub-query, get the # of > descendants, and save that count as an integer in the sphinx index for > each record. > > Any ideas? > > Thanks! > Cary > > -- > You received this message because you are subscribed to the Google Groups > "Thinking Sphinx" group. > 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 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 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.
