One strategy is to denormalize all the way. That is, each Solr "document" is Gift Amount and Gift Date would not be multiValued. You'd create a different "document" for each gift, so you'd have multiple documents with the same Id, Name, and Address. Be careful, though, if you've defined Id as a UniqueKey, you'd only have one record/donor. You can handle this easily enough by making a composite key of Id+Gift Date (assuming no donor made more than one gift on exactly the same date).
I know this goes completely against all the reflexes you've built up with working with DBs, but... Can you give us a clue how many donations we're talking about here? You'd have to be working with a really big nonprofit to get enough documents to have to start worrying about making your index smaller. HTH Erick On Wed, Sep 15, 2010 at 1:41 PM, Andre Bickford <abickf...@softrek.com>wrote: > 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 > > >
