Ways to search across multple assocations

[Jake]

I am looking for a good way to index and search across multiple has_many associations.  I did something like this before by searching on one model simply to gather all of the foreign keys and then searched on another model adding in the foreign keys as a filter.  I could do something like that again, but it didn't seem right when I did it before and it still doesn't sit well with me.

So here's the simplified version of my current situation.  I have study subjects of several types that are potentially enrolled in several projects.  For of these enrollments, the subject has or has not consented and is or is not eligible.  In addition, the subjects may or may not have provided a variety of types of samples, like blood or bone marrow.

class StudySubject < ActiveRecord::Base
        has_many :enrollments
        has_many :samples
        #    relevant attributes :type
end
class Enrollment < ActiveRecord::Base
        belongs_to :study_subject
        belongs_to :project
        #    relevant attributes :eligible, :consented
end
class Sample < ActiveRecord::Base
        belongs_to :study_subject
        #    relevant attributes :type
end
class Project < ActiveRecord::Base
        has_many :enrollments
        #    relevant attributes :name
end

One of the many types of questions that will need answered is ...

Which 'case' type study_subjects have an 'eligible' AND 'consented' enrollment in some project 'X' AND have provided a 'blood' sample?

As I stated at the beginning, I could grab all the study_subject_ids from a

Sample.search{ with(:type,'blood') }

and all the study_subject_ids from a

Enrollment.search do
with(:eligible,true)
with(:consented,true)
with(:project,'X')
end

and then use them in

StudySubject.search do
with(:type,'Case')
with(:study_subject_id).any_of( sample_study_subject_ids & enrollment_study_subject_ids)
end

but this doesn't seem kosher.  Plus, I'd need to not paginate so I'd get all of the study_subject_ids on the pre-search searches.  This technique also doesn't provide with real facet counts.

If the case was just one has_many relationship, I could search in the other direction, but with more than one, this has gotten a bit complicated.  And I suspect that there will be more associations to include in this.

Thoughts?  Suggestions?  Words of wisdom?  Perhaps an alternative to sunspot and solr that can better index and facet in this situation?

As always, thanks in advance.