I know it goes against the grain here for a DB person, but... denormalize. Really. Solr does many things well, but whenever you start trying to make it do database-like stuff you need to back up and re-think things.....
Simplest thing: Try indexing one record for each customer/purchase/complaint triplet. How many records are we talking here anyway? 30-40M documents will probably perform admirably on even a small piece of hardware. Best Erick On Mon, Mar 12, 2012 at 12:55 AM, Angelyna Bola <angelyna.b...@gmail.com> wrote: > Bill, > > So sorry - my example is rapidly showing its short comings. The data I > am actually working with is complex and obscure so I was trying to > think of an example that was easy to relate to, but still has all the > relevant characteristics. > > Let me try a better example: > > Let's suppose a Company is selling products and keeps track of > complaints (which do not relate to any specific purchase): > > Data: > > Table #1: CUSTOMERS (parent table) > City > State > Zip > > Table #2: PURCHASES (child table with foreign key to CUSTOMERS) > Date > Product Type > Quantity > > Table #3: COMPLAINTS (child table with foreign key to CUSTOMERS) > Date > Complaint Type > Complaint Text > Remediation > > And the company wants to be able to query how their customers buy > products and complaints. > > The tricky part is company needs to be able to blend string queries > with date range queires and integer range queries. > > Query: > > CUSTOMERS in Vermont > and > PURCHASES within the last 1 year with a Quantity > 75 > and > COMPLAINTS within the last 2 years with a Complaint Type = XYZ and > Complaint Text contains the words ABC and EFG > > Problem: > > The problem with multi-valued fields is I loose the ability to do > range queries over numeric attributes (such as Quantity or Date) when > they only relate to other specific attributes (such as Product or > Service Type). > > With the Join feature in Solr Trunk, I have no problem joining > CUSTOMERS to PURCHASES or alternatively joining CUSTOMERS to > COMPLAINTS. But I do not see a way of joining across all three. > > Hopefully I have done a better job with this example (appreciate your > patience in trying to help me - I am not always the best at > explaining). > > Angelyna
