lucene-solr-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andre Bickford <abickf...@softrek.com>
Subject Re: Simple Filter Query (fq) Use Case Question
Date Thu, 16 Sep 2010 01:00:55 GMT
Hi Jonathan,

Thank you very much for your creative suggestions. I also wondered if perhaps combining giftDate
and giftAmount into one single token was a possible solution. I'll definitely explore this
further using your ideas. I especially like your idea of combing the giftDate and giftAmount
 into the format '2007-10000000'.

Thanks again,
Andre

On Sep 15, 2010, at 5:55 PM, Jonathan Rochkind wrote:

> I might consider what Erick suggested to actually be 'normalization' rather than de-normalization!
 It's just that in Solr you only get one 'table'.
> 
> Here's yet another approach, which will have it's own trade-offs:
> 
> Keep the document as it is, representing a donor. But in addition to indexing in the
multi-valued 'amount' and 'date' fields like you're doing (which will still be neccesary/useful
for some kinds of queries), create a multi-valued "date-amount" field, where you index tokens
like:
> 
> "200-11/14/2006".
> 
> Hmm, I'm actually not sure what that will get you, now that I'm here. Especially with
this case that involves dates, which can be especially tricky.  But combining two values in
a de-normalized way is often an approach with solr for this sort of question, so I'll put
it out there anyway in case it gives you or anyone ideas. Might or might not get you further
if in that combined fields you put things into buckets, if you really aren't going to need
to query down to the day, "200.11-2006", or even an integer representation of "Nov 2006".
> 
> 
> 
> Andre Bickford wrote:
>> Thanks for the response Erick.
>> 
>> I did actually try exactly what you suggested. I flipped the index over so that a
gift is the document. This solution certainly solves the previous problem, but introduces
a new issue where the search results show duplicate donors. If a donor gave 12 times in a
year, and we offer full years as facet ranges, my understanding is that you'd see that donor
12 times in the search results, once for each gift document. Obviously I could do some client
side filtering to list only distinct donors, but I was hoping to avoid that.
>> 
>> If I've simply stumbled into the basic tradeoffs of denormalization, I can live with
client side de-duplication, but if you have any further suggestions I'm all eyes.
>> 
>> As for sizing, we have some huge charities as clients. However, right now I'm testing
on a copy of prod data from a smaller client with ~350,000 donors and ~8,000,000 gift records.
So, when I "flipped" the index around as you suggested, it went from 350,000 documents to
8,000,000 documents. No issues with performance at all.
>> 
>> Thanks again,
>> Andre
>> 
>> -----Original Message-----
>> From: Erick Erickson [mailto:erickerickson@gmail.com] Sent: Wednesday, September
15, 2010 3:09 PM
>> To: solr-user@lucene.apache.org
>> Subject: Re: Simple Filter Query (fq) Use Case Question
>> 
>> 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 <abickford@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
>>> abickford@softrek.com  www.softrek.com
>>> 
>>> 
>>> 
>>>    
>> 
>> 
>> 
>>  


Mime
View raw message