I'm working on creating a solr index search for a charitable organization. The solr index stores documents of donors. Each donor document has the following four fields:
Id Name Address Gift Amount (multiValued) Gift Date (multiValued) In our relational database, there is a one-to-many relationship between the DONOR table and the GIFT table. One donor can of course give many gifts over time. Consequently, I created the Gift Amount and Gift Date fields to be mutiValued. Now, consider the following query filtered for gifts last month between $0 and $100: q=name:Jones fq=giftDate:[NOW/MONTH-1 TO NOW/MONTH] fq=giftAmount:[0 TO 100] The results show me donors who donated ANY amount in the past month and donors who had EVER in the past given a gift between $0 and $100. I was hoping to only see donors who had given a gift between $0 and $100 in the past month exclusively. I believe the problem is that I neglected to consider that for two multiValued fields, while the values might align "index wise", there is really no other association between the two fields, so the filter query intersection isn't really behaving as I expected. I think this is a fundamental question of one-to-many denormalization, but obviously I'm not yet experienced enough with Lucene/Solr to find a solution. As to why not just keep using a relational database, it's because I'm trying to provide a faceting solution to "drill down" to donors. The aforementioned fq parameters would come from faceting. Oh, that and Oracle Text indexes are a PITA. :-) Thanks for any help you can provide. André Bickford Software Engineering Team Leader SofTrek Corporation 30 Bryant Woods North Amherst, NY 14228 716.691.2800 x154 800.442.9211 Fax: 716.691.2828 abickf...@softrek.com www.softrek.com
