Thanks so much for your replay, Pat. I will give this a shot. Thanks also for clarifying what is allowed in the custom SQL.
Josh On Friday, December 12, 2014 8:35:47 PM UTC-6, Pat Allan wrote: > > Hi Josh > > This is untested, but I’d try the following in your index: > > # force both association joins > join sponsorships > join involved_sponsorships > > has ‘COUNT(DISTINCT sponsorships.id) + COUNT(DISTINCT > involved_sponsorships.id)’, > :as => :sponsorship_count, :type => :integer > > Note that the join table names may need to be updated - look at the > generated joins in config/development.sphinx.conf. > > Is it possible for a constituent to have the same sponsorship object via > both associations? If so, and you don’t want those sponsorships counted > twice, something more complex is required. The solution below is *only* for > PostgreSQL - I’m not sure what a MySQL solution would look like. > > # for PostgreSQL, using the intarray extension: > # http://stackoverflow.com/a/24149630/54500 > has ‘array_length(array_agg(DISTINCT sponsorships.id) | > array_agg(DISTINCT involved_sponsorships.id), 1)’, > :as => :sponsorship_count, :type => :integer > > As for custom SQL snippets in index definitions - you’ve got it right, > it’s a string with SQL inside it. The SQL can be whatever you wish that’s > valid within a SELECT clause for your given database. > > Cheers > > — > Pat > > On 12 Dec 2014, at 6:26 pm, Josh Lehman <[email protected] > <javascript:>> wrote: > > I have a model that includes the follow two associations: > > class Constituent > has_many :involved_sponsorships, :class_name => "Sponsorship", > :foreign_key => "paying_constituent_id" > has_many :sponsorships > > > The related Sponsorships model includes the following two relationships: > > class Sponsorship > belongs_to :constituent > belongs_to :paying_constituent, :class_name => "Constituent", > :foreign_key => "paying_constituent_id" > > --- > > I need to create an index that will allow me to search based on a count of > sponsorships, BUT sponsorships in this context needs to include the total > quantity of BOTH the involved_sponsorships association and the sponsorships > association. > > I know I can create the following filter which will count ONLY the > sponsorships associations: > > has 'COUNT(sponsorships.id)', :as => :sponsorship_count, :type => :integer > > I'm struggling to find a summary of how I might structure the custom SQL > here in the index to essentially handle joining the sponsorships count to > the involved_sponsorships count and index that as the :sponsorship_count > > Hard to imagine this isn't something anybody else has tried to tackle but > I've come up fairly empty-handed in all my searches for a comparable > situation or example. On a related note, is there any documentation > specifically about the custom SQL syntax available in building these > indexes? Thanks for any help anybody is able to provide! > > > > > > -- > You received this message because you are subscribed to the Google Groups > "Thinking Sphinx" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected] <javascript:>. > To post to this group, send email to [email protected] > <javascript:>. > Visit this group at http://groups.google.com/group/thinking-sphinx. > For more options, visit https://groups.google.com/d/optout. > > > -- You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/thinking-sphinx. For more options, visit https://groups.google.com/d/optout.
