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
