You are viewing a plain text version of this content. The canonical link for it is here.
Posted to solr-user@lucene.apache.org by Angelina Bola <an...@gmail.com> on 2012/03/11 01:58:11 UTC
3 Way Solr Join . . ?
Does "Solr" support a 3-way join? i.e.
http://wiki.apache.org/solr/Join (I have the 2-way join working)
For example, I am pulling 3 different tables from a RDBMS into one Solr core:
Table#1: Customers (parent table)
Table#2: Addresses (child table with foreign key to customers)
Table#3: Phones (child table with foreign key to customers)
with a ONE to MANY relationship between:
Customers and Addresses
Customers and Phones
When I pull them into Solr I cannot denormalize the relationships as a
given customers can have many addresses and many phones.
When they come into the my single core (customerInfo), each document
gets a customerInfo_type and a uid corresponding to that type, for
example:
Customer Document
customerInfo_type='customer'
customer_id
Address Document
customerInfo_type='address'
fk_address_customer_id
Phone Document
customerInfo_type='phone'
fk_phone_customer_id
Logically, I need to query in Solr for Customers who:
- Have an address in a given state
- Have a phone in a given area code
- Are a given gender
Syntactically, it would think it would look like:
- http://localhost:8983/solr/customerInfo/select/?
q={!join from=fk_address_customer_id to=customer_id}address_State:Maine&
fq={!join from=customer_id to=fk_phone_customer_id}phone_area_code:212&
fq=customer_gender:female
But that does not work for me.
Appreciate any thoughts,
Angelyna
Re: 3 Way Solr Join . . ?
Posted by William Bell <bi...@gmail.com>.
Yeah I am a bit afraid when people want to use the join() feature. To
get good performance you really need to try to stick to the
recommendation of denormalizing your database into multiValued search
fields.
You can also use external fields, or store formatted info into a
String field in json or xml format.
On Sat, Mar 10, 2012 at 6:22 PM, Walter Underwood <wu...@wunderwood.org> wrote:
> Fields can be multi-valued. Put multiple phone numbers in a field and match all of them.
>
> wunder
>
> On Mar 10, 2012, at 4:58 PM, Angelina Bola wrote:
>
>> Does "Solr" support a 3-way join? i.e.
>> http://wiki.apache.org/solr/Join (I have the 2-way join working)
>>
>> For example, I am pulling 3 different tables from a RDBMS into one Solr core:
>>
>> Table#1: Customers (parent table)
>> Table#2: Addresses (child table with foreign key to customers)
>> Table#3: Phones (child table with foreign key to customers)
>>
>> with a ONE to MANY relationship between:
>>
>> Customers and Addresses
>> Customers and Phones
>>
>> When I pull them into Solr I cannot denormalize the relationships as a
>> given customers can have many addresses and many phones.
>>
>> When they come into the my single core (customerInfo), each document
>> gets a customerInfo_type and a uid corresponding to that type, for
>> example:
>>
>> Customer Document
>> customerInfo_type='customer'
>> customer_id
>>
>> Address Document
>> customerInfo_type='address'
>> fk_address_customer_id
>>
>> Phone Document
>> customerInfo_type='phone'
>> fk_phone_customer_id
>>
>> Logically, I need to query in Solr for Customers who:
>>
>> - Have an address in a given state
>> - Have a phone in a given area code
>> - Are a given gender
>>
>> Syntactically, it would think it would look like:
>>
>> - http://localhost:8983/solr/customerInfo/select/?
>> q={!join from=fk_address_customer_id to=customer_id}address_State:Maine&
>> fq={!join from=customer_id to=fk_phone_customer_id}phone_area_code:212&
>> fq=customer_gender:female
>>
>> But that does not work for me.
>>
>> Appreciate any thoughts,
>>
>> Angelyna
>
> --
> Walter Underwood
> wunder@wunderwood.org
>
>
>
--
Bill Bell
billnbell@gmail.com
cell 720-256-8076
Re: 3 Way Solr Join . . ?
Posted by Walter Underwood <wu...@wunderwood.org>.
Fields can be multi-valued. Put multiple phone numbers in a field and match all of them.
wunder
On Mar 10, 2012, at 4:58 PM, Angelina Bola wrote:
> Does "Solr" support a 3-way join? i.e.
> http://wiki.apache.org/solr/Join (I have the 2-way join working)
>
> For example, I am pulling 3 different tables from a RDBMS into one Solr core:
>
> Table#1: Customers (parent table)
> Table#2: Addresses (child table with foreign key to customers)
> Table#3: Phones (child table with foreign key to customers)
>
> with a ONE to MANY relationship between:
>
> Customers and Addresses
> Customers and Phones
>
> When I pull them into Solr I cannot denormalize the relationships as a
> given customers can have many addresses and many phones.
>
> When they come into the my single core (customerInfo), each document
> gets a customerInfo_type and a uid corresponding to that type, for
> example:
>
> Customer Document
> customerInfo_type='customer'
> customer_id
>
> Address Document
> customerInfo_type='address'
> fk_address_customer_id
>
> Phone Document
> customerInfo_type='phone'
> fk_phone_customer_id
>
> Logically, I need to query in Solr for Customers who:
>
> - Have an address in a given state
> - Have a phone in a given area code
> - Are a given gender
>
> Syntactically, it would think it would look like:
>
> - http://localhost:8983/solr/customerInfo/select/?
> q={!join from=fk_address_customer_id to=customer_id}address_State:Maine&
> fq={!join from=customer_id to=fk_phone_customer_id}phone_area_code:212&
> fq=customer_gender:female
>
> But that does not work for me.
>
> Appreciate any thoughts,
>
> Angelyna
--
Walter Underwood
wunder@wunderwood.org
Re: 3 Way Solr Join . . ?
Posted by Sabeer Hussain <sh...@del.aithent.com>.
I have a similar situation in my application. I have five different entities.
The relationships among entities as follows
Protocol --> ( zero or more) Study --> ( zero or more) Patient
Protocol --> ( zero or more) Drug
Patient --> (zero or more) Study
Form --> (zero or many) Study
Moreover, all these entities can be exist independently also (as per the
requirement of my application). So, I cannot create a document to include
all these entities using demoralization. If I need to find out the Drug
Name (from Drug entity), Protocol Name (from Protocol entity), Study Name
(from Study entity), Patient Name (from Patient entity) and Form Name ( from
Form entity) based on Drug Batch Number (from Drug entity) I passed. Using
Join in Solr, I can get either child or parent not from both. What is the
best way to index the data in Solr? Do I need to create separate indices for
each entity or single one for all
--
View this message in context: http://lucene.472066.n3.nabble.com/3-Way-Solr-Join-tp3815979p3990515.html
Sent from the Solr - User mailing list archive at Nabble.com.