lucene-solr-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Andre Bickford" <abickf...@softrek.com>
Subject Simple Filter Query (fq) Use Case Question
Date Wed, 15 Sep 2010 17:41:25 GMT
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
abickford@softrek.com  www.softrek.com



Mime
View raw message