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]> 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]
> <mailto:[email protected]>.
> To post to this group, send email to [email protected]
> <mailto:[email protected]>.
> Visit this group at http://groups.google.com/group/thinking-sphinx
> <http://groups.google.com/group/thinking-sphinx>.
> For more options, visit https://groups.google.com/d/optout
> <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.
