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 mihaela olteanu <mi...@yahoo.com> on 2013/07/06 20:22:06 UTC
Solr 4.x union of cross-joins
Hello,
I have 3 indices that form a hierarchy. Basically these were constructed from 3 tables: parent, child1 and child2 and between parent and children there is a one to many relationship.
parent (id,name)
child1(id,join_key,field1)
child2(id,join_key,field2)
"join_key" is the foreign key in each of the child tables
I would like to perform searches like the following: "give me the parents whose name is x or whose child1.field1 is y or child2.field2 is z".
I tried with a query like the following:
#first attempt
/parent/select?q=(name:x) OR ({!join from=join_key to=id fromIndex=child1}field1:y) OR ({!join from=join_key to=id fromIndex=child2}field2:z)
but it didn't work.
If I put all these documents in a single index and I add also a type field whose value would be "Parent" for documents that come from parent table, "Child1" and "Child2" for the documents that come from the table child1 and child2 respectively I can perform the following query to get the results that I want:
/parent/select?q={!join from=join_key to=id}field1:y OR field2:z
The most important thing about this query is that I want to have back as results only documents of type "Parent"
With the previous query I could not capture though also the search of parents whose name equals "x", but if i do a small trick and add a "join_key" field in documents of type "Parent" whose value equals the value in "id" field then I could write something like:
/parent/select?q={!join from=join_key to=id}field1:y OR field2:z OR name:x
and had all the results that I wanted.
But in my use case it makes more sense to have 3 different indices and not storing all the documents in the same index.
Does anyone know if I can somehow enable the first query that I gave as example above? I would say that it would be great to have such a feature in the next versions of Solr 4.x
Thanks,
Mihaela
Re: Solr 4.x union of cross-joins
Posted by mihaela olteanu <mi...@yahoo.com>.
It seems that I lost the formatting of the data.
parent
idKEY_SCOMMENT_TTYPE_S
11ventilation testParent
22comment2Parent
33comment3Parent
child1
idKEY_SCOMMENT_TTYPE_S
41comment4Child1
52ventilation testChild1
63comment5Child1
child2
idKEY_SCOMMENT_TTYPE_S
71comment6Child2
82comment7Child2
93ventilation testChild2
103ventilation testChild2
Thanks
Mihaela
________________________________
From: mihaela olteanu <mi...@yahoo.com>
To: Yonik Seeley <yo...@lucidworks.com>
Cc: "solr-user@lucene.apache.org" <so...@lucene.apache.org>
Sent: Monday, July 8, 2013 3:52 PM
Subject: Re: Solr 4.x union of cross-joins
Here is exactly the data that I'm working with and the results for some tests that I performed:
parent child1 child2
id KEY_S COMMENT_T TYPE_S id KEY_S COMMENT_T TYPE_S id KEY_S COMMENT_T TYPE_S
1 1 ventilation test Parent 4 1 comment4 Child1 7 1 comment6 Child2
2 2 comment2 Parent 5 2 ventilation test
Child1 8 2 comment7 Child2
3 3 comment3 Parent 6 3 comment5 Child1 9 3 ventilation test
Child2
10 3 ventilation test
Child2
I am using dynamic fields and here is their definition:
<dynamicField name="*_S" type="string" indexed="true" stored="true"/>
<dynamicField name="*_T" type="textUnicode" indexed="true" stored="true" />
<fieldType name="textUnicode" class="solr.TextField" positionIncrementGap="100" storeOffsetsWithPositions="true">
<analyzer type="index">
<tokenizer class="solr.StandardTokenizerFactory"/>
<filter class="solr.ICUFoldingFilterFactory"/>
<filter class="solr.StopFilterFactory" ignoreCase="true" words="stopwords.txt"/>
<filter class="solr.LimitTokenCountFilterFactory" maxTokenCount="300000"/>
<filter class="solr.SnowballPorterFilterFactory" language="English"/>
<filter class="solr.RemoveDuplicatesTokenFilterFactory"/>
</analyzer>
<analyzer type="query">
<tokenizer class="solr.StandardTokenizerFactory"/>
<filter class="solr.ICUFoldingFilterFactory"/>
<filter class="solr.StopFilterFactory" ignoreCase="true" words="stopwords.txt"/>
<filter class="solr.SnowballPorterFilterFactory" language="English"/>
</analyzer>
</fieldType>
Test scenarios:
1. only one join in the query
q={!join from=KEY_S to=KEY_S fromIndex=child1}(COMMENT_T:ventilation TYPE_S:Child1)&q.op=AND
JoinQuery({!join from=KEY_S to=KEY_S fromIndex=child1}+COMMENT_T:ventil +TYPE_S:Child1)
q={!join from=KEY_S to=KEY_S fromIndex=child1}(COMMENT_T:("some text") AND TYPE_S:Child1)&q.op=OR
JoinQuery({!join from=KEY_S to=KEY_S fromIndex=child1}+COMMENT_T:"ventil test" +TYPE_S:Child1)
- conclusion: I can write any type of query for the joined index
2. Union of two cross join queries
2.1) parent inner join child1 on KEY_S=KEY_S where COMMENT_T = "ventilation test" and TYPE_S="Child1" OR parent inner join child2 on KEY_S=KEY_S where COMMENT_T = "ventilation"
a) - returns the expected results
q={!join from=KEY_S to=KEY_S fromIndex=child1}(COMMENT_T:("ventilation test") AND TYPE_S:Child1) {!join from=KEY_S to=KEY_S fromIndex=child2}COMMENT_T:ventilation&q.op=OR
JoinQuery({!join from=KEY_S to=KEY_S fromIndex=child1}(+COMMENT_T:"ventil test" +TYPE_S:Child1) {!join from=KEY_S to=KEY_S fromIndex=child2}COMMENT_T:ventil)
Results
{id=2, KEY_S=2, TYPE_S=Parent, COMMENT_T=comment2, _version_=1439995636607877120, timestamp=Mon Jul 08 08:34:14 EDT 2013}
{id=3, KEY_S=3, TYPE_S=Parent, COMMENT_T=comment3, _version_=1439995636627800064, timestamp=Mon Jul 08 08:34:14 EDT 2013}
2.2) parent inner join child1 on KEY_S=KEY_S where COMMENT_T = "ventilation test" and TYPE_S="Child1" OR parent inner join child1 on KEY_S=KEY_S where COMMENT_T = "ventilation" and TYPE_S="Child2"
a) - no results - but it should
q={!join from=KEY_S to=KEY_S fromIndex=child1}(COMMENT_T:("ventilation test") AND TYPE_S:Child1) {!join from=KEY_S to=KEY_S fromIndex=child2}COMMENT_T:ventilation AND TYPE_S:Child2&q.op=OR
JoinQuery({!join from=KEY_S to=KEY_S fromIndex=child1}(+COMMENT_T:"ventil test" +TYPE_S:Child1) +{!join from=KEY_S to=KEY_S fromIndex=child2}COMMENT_T:ventil +TYPE_S:Child2)
b) - no results- but it should
q={!join from=KEY_S to=KEY_S fromIndex=child1}(COMMENT_T:("ventilation test") AND TYPE_S:Child1) OR {!join from=KEY_S to=KEY_S fromIndex=child2}COMMENT_T:ventilation AND TYPE_S:Child2&q.op=OR
JoinQuery({!join from=KEY_S to=KEY_S fromIndex=child1}(+COMMENT_T:"ventil test" +TYPE_S:Child1) +{!join from=KEY_S to=KEY_S fromIndex=child2}COMMENT_T:ventil +TYPE_S:Child2)
c) - parse error for the bellow example (could not add brackets for grouping the query for the child2 joined index)
{!join from=KEY_S to=KEY_S fromIndex=child1}(COMMENT_T:("ventilation test") AND TYPE_S:Child1) OR {!join from=KEY_S to=KEY_S fromIndex=child2}(COMMENT_T:ventilation AND TYPE_S:Child2)
d) - returns the expected results
{!join from=KEY_S to=KEY_S fromIndex=child1}(COMMENT_T:("ventilation test") AND TYPE_S:Child1) OR _query_:"{!join from=KEY_S to=KEY_S fromIndex=child2}(COMMENT_T:ventilation AND TYPE_S:Child2)"
JoinQuery({!join from=KEY_S to=KEY_S fromIndex=child1}(+COMMENT_T:"ventil test" +TYPE_S:Child1) {!join from=KEY_S to=KEY_S fromIndex=child2}+COMMENT_T:ventil +TYPE_S:Child2)
Results
{id=2, KEY_S=2, TYPE_S=Parent, COMMENT_T=comment2, _version_=1439995636607877120, timestamp=Mon Jul 08 08:34:14 EDT 2013}
{id=3, KEY_S=3, TYPE_S=Parent, COMMENT_T=comment3, _version_=1439995636627800064, timestamp=Mon Jul 08 08:34:14 EDT 2013}
2.3) select from parent where COMMENT_T="ventilation test" and TYPE_S="Parent" OR parent inner join child1 on KEY_S=KEY_S where COMMENT_T = "ventilation test" and TYPE_S="Child1" OR parent inner join child1 on KEY_S=KEY_S where COMMENT_T = "ventilation" and TYPE_S="Child2"
a) - no results from parent althought it should
{!join from=KEY_S to=KEY_S fromIndex=child1}(COMMENT_T:("ventilation test") AND TYPE_S:Child1) OR _query_:"{!join from=KEY_S to=KEY_S fromIndex=child2}(COMMENT_T:ventilation AND TYPE_S:Child2)" OR _query_:"COMMENT_T:(ventilation test) AND TYPE_S:Parent"
JoinQuery({!join from=KEY_S to=KEY_S fromIndex=child1}(+COMMENT_T:"ventil test" +TYPE_S:Child1) {!join from=KEY_S to=KEY_S fromIndex=child2}+COMMENT_T:ventil +TYPE_S:Child2 (+(COMMENT_T:ventil COMMENT_T:test) +TYPE_S:Parent))
b) - parser errors
_query_:"COMMENT_T:(ventilation test) AND TYPE_S:Parent" OR {!join from=KEY_S to=KEY_S fromIndex=child1}(COMMENT_T:("some text") AND TYPE_S:Child1) OR _query_:"{!join from=KEY_S to=KEY_S fromIndex=child2}(COMMENT_T:ventilation AND TYPE_S:Child2)"
c) - all expected results (from parent as well)
_query_:"COMMENT_T:(ventilation test) AND TYPE_S:Parent" OR _query_:"{!join from=KEY_S to=KEY_S fromIndex=child1}(COMMENT_T:(ventilation test) AND TYPE_S:Child1)" OR _query_:"{!join from=KEY_S to=KEY_S fromIndex=child2}(COMMENT_T:ventilation AND TYPE_S:Child2)"
(+(COMMENT_T:ventil COMMENT_T:test) +TYPE_S:Parent) JoinQuery({!join from=KEY_S to=KEY_S fromIndex=child1}+(COMMENT_T:ventil COMMENT_T:test) +TYPE_S:Child1) JoinQuery({!join from=KEY_S to=KEY_S fromIndex=child2}+COMMENT_T:ventil +TYPE_S:Child2)
_query_:"{!join from=KEY_S to=KEY_S fromIndex=child1}(COMMENT_T:(ventilation test) AND TYPE_S:Child1)" OR _query_:"{!join from=KEY_S to=KEY_S fromIndex=child2}(COMMENT_T:ventilation AND TYPE_S:Child2)" OR _query_:"COMMENT_T:(ventilation test) AND TYPE_S:Parent"
JoinQuery({!join from=KEY_S to=KEY_S fromIndex=child1}+(COMMENT_T:ventil COMMENT_T:test) +TYPE_S:Child1) JoinQuery({!join from=KEY_S to=KEY_S fromIndex=child2}+COMMENT_T:ventil +TYPE_S:Child2) (+(COMMENT_T:ventil COMMENT_T:test) +TYPE_S:Parent)
Results:
{id=1, KEY_S=1, TYPE_S=Parent, COMMENT_T=ventilation test, _version_=1439995541507276800, timestamp=Mon Jul 08 08:32:43 EDT 2013}
{id=2, KEY_S=2, TYPE_S=Parent, COMMENT_T=comment2, _version_=1439995541529296896, timestamp=Mon Jul 08 08:32:43 EDT 2013}
{id=3, KEY_S=3, TYPE_S=Parent, COMMENT_T=comment3, _version_=1439995541534539776, timestamp=Mon Jul 08 08:32:43 EDT 2013}
As a conclusion, I can write any query I want with union of multiple join and union with documents taken from the parent index only if I use _query_ for each individual piece from the query (one _query_ when querying the parent and one _query_ for each different join query). Unfortunately when using _query_ I cannot write "exact match" queries because I cannot add the query string in " (quotes) (I already add quotes for the query: _query_:"<actual query>").
I would say that the scenarios 2.2 (a-c) and 2.3 (a-b) should work as well. If those are not bugs of the query parser what syntax shall I use?
Thanks in advance!
Mihaela
________________________________
From: Yonik Seeley <yo...@lucidworks.com>
To: "solr-user@lucene.apache.org" <so...@lucene.apache.org>; mihaela olteanu <mi...@yahoo.com>
Sent: Saturday, July 6, 2013 10:31 PM
Subject: Re: Solr 4.x union of cross-joins
On Sat, Jul 6, 2013 at 2:22 PM, mihaela olteanu <mi...@yahoo.com> wrote:
> Hello,
>
> I have 3 indices that form a hierarchy. Basically these were constructed from 3 tables: parent, child1 and child2 and between parent and children there is a one to many relationship.
> parent (id,name)
>
> child1(id,join_key,field1)
>
> child2(id,join_key,field2)
>
>
> "join_key" is the foreign key in each of the child tables
>
> I would like to perform searches like the following: "give me the parents whose name is x or whose child1.field1 is y or child2.field2 is z".
>
> I tried with a query like the following:
> #first attempt
> /parent/select?q=(name:x) OR ({!join from=join_key to=id fromIndex=child1}field1:y) OR ({!join from=join_key to=id fromIndex=child2}field2:z)
>
> but it didn't work.
Any idea why?
Try adding debugQuery=true to see if the query is parsed correctly.
Try executing a single join alone, like
q={!join from=join_key to=id fromIndex=child1}field1:y
to see if the results are as expected.
-Yonik
http://lucidworks.com
Re: Solr 4.x union of cross-joins
Posted by mihaela olteanu <mi...@yahoo.com>.
Here is exactly the data that I'm working with and the results for some tests that I performed:
parent child1 child2
id KEY_S COMMENT_T TYPE_S id KEY_S COMMENT_T TYPE_S id KEY_S COMMENT_T TYPE_S
1 1 ventilation test Parent 4 1 comment4 Child1 7 1 comment6 Child2
2 2 comment2 Parent 5 2 ventilation test
Child1 8 2 comment7 Child2
3 3 comment3 Parent 6 3 comment5 Child1 9 3 ventilation test
Child2
10 3 ventilation test
Child2
I am using dynamic fields and here is their definition:
<dynamicField name="*_S" type="string" indexed="true" stored="true"/>
<dynamicField name="*_T" type="textUnicode" indexed="true" stored="true" />
<fieldType name="textUnicode" class="solr.TextField" positionIncrementGap="100" storeOffsetsWithPositions="true">
<analyzer type="index">
<tokenizer class="solr.StandardTokenizerFactory"/>
<filter class="solr.ICUFoldingFilterFactory"/>
<filter class="solr.StopFilterFactory" ignoreCase="true" words="stopwords.txt"/>
<filter class="solr.LimitTokenCountFilterFactory" maxTokenCount="300000"/>
<filter class="solr.SnowballPorterFilterFactory" language="English"/>
<filter class="solr.RemoveDuplicatesTokenFilterFactory"/>
</analyzer>
<analyzer type="query">
<tokenizer class="solr.StandardTokenizerFactory"/>
<filter class="solr.ICUFoldingFilterFactory"/>
<filter class="solr.StopFilterFactory" ignoreCase="true" words="stopwords.txt"/>
<filter class="solr.SnowballPorterFilterFactory" language="English"/>
</analyzer>
</fieldType>
Test scenarios:
1. only one join in the query
q={!join from=KEY_S to=KEY_S fromIndex=child1}(COMMENT_T:ventilation TYPE_S:Child1)&q.op=AND
JoinQuery({!join from=KEY_S to=KEY_S fromIndex=child1}+COMMENT_T:ventil +TYPE_S:Child1)
q={!join from=KEY_S to=KEY_S fromIndex=child1}(COMMENT_T:("some text") AND TYPE_S:Child1)&q.op=OR
JoinQuery({!join from=KEY_S to=KEY_S fromIndex=child1}+COMMENT_T:"ventil test" +TYPE_S:Child1)
- conclusion: I can write any type of query for the joined index
2. Union of two cross join queries
2.1) parent inner join child1 on KEY_S=KEY_S where COMMENT_T = "ventilation test" and TYPE_S="Child1" OR parent inner join child2 on KEY_S=KEY_S where COMMENT_T = "ventilation"
a) - returns the expected results
q={!join from=KEY_S to=KEY_S fromIndex=child1}(COMMENT_T:("ventilation test") AND TYPE_S:Child1) {!join from=KEY_S to=KEY_S fromIndex=child2}COMMENT_T:ventilation&q.op=OR
JoinQuery({!join from=KEY_S to=KEY_S fromIndex=child1}(+COMMENT_T:"ventil test" +TYPE_S:Child1) {!join from=KEY_S to=KEY_S fromIndex=child2}COMMENT_T:ventil)
Results
{id=2, KEY_S=2, TYPE_S=Parent, COMMENT_T=comment2, _version_=1439995636607877120, timestamp=Mon Jul 08 08:34:14 EDT 2013}
{id=3, KEY_S=3, TYPE_S=Parent, COMMENT_T=comment3, _version_=1439995636627800064, timestamp=Mon Jul 08 08:34:14 EDT 2013}
2.2) parent inner join child1 on KEY_S=KEY_S where COMMENT_T = "ventilation test" and TYPE_S="Child1" OR parent inner join child1 on KEY_S=KEY_S where COMMENT_T = "ventilation" and TYPE_S="Child2"
a) - no results - but it should
q={!join from=KEY_S to=KEY_S fromIndex=child1}(COMMENT_T:("ventilation test") AND TYPE_S:Child1) {!join from=KEY_S to=KEY_S fromIndex=child2}COMMENT_T:ventilation AND TYPE_S:Child2&q.op=OR
JoinQuery({!join from=KEY_S to=KEY_S fromIndex=child1}(+COMMENT_T:"ventil test" +TYPE_S:Child1) +{!join from=KEY_S to=KEY_S fromIndex=child2}COMMENT_T:ventil +TYPE_S:Child2)
b) - no results- but it should
q={!join from=KEY_S to=KEY_S fromIndex=child1}(COMMENT_T:("ventilation test") AND TYPE_S:Child1) OR {!join from=KEY_S to=KEY_S fromIndex=child2}COMMENT_T:ventilation AND TYPE_S:Child2&q.op=OR
JoinQuery({!join from=KEY_S to=KEY_S fromIndex=child1}(+COMMENT_T:"ventil test" +TYPE_S:Child1) +{!join from=KEY_S to=KEY_S fromIndex=child2}COMMENT_T:ventil +TYPE_S:Child2)
c) - parse error for the bellow example (could not add brackets for grouping the query for the child2 joined index)
{!join from=KEY_S to=KEY_S fromIndex=child1}(COMMENT_T:("ventilation test") AND TYPE_S:Child1) OR {!join from=KEY_S to=KEY_S fromIndex=child2}(COMMENT_T:ventilation AND TYPE_S:Child2)
d) - returns the expected results
{!join from=KEY_S to=KEY_S fromIndex=child1}(COMMENT_T:("ventilation test") AND TYPE_S:Child1) OR _query_:"{!join from=KEY_S to=KEY_S fromIndex=child2}(COMMENT_T:ventilation AND TYPE_S:Child2)"
JoinQuery({!join from=KEY_S to=KEY_S fromIndex=child1}(+COMMENT_T:"ventil test" +TYPE_S:Child1) {!join from=KEY_S to=KEY_S fromIndex=child2}+COMMENT_T:ventil +TYPE_S:Child2)
Results
{id=2, KEY_S=2, TYPE_S=Parent, COMMENT_T=comment2, _version_=1439995636607877120, timestamp=Mon Jul 08 08:34:14 EDT 2013}
{id=3, KEY_S=3, TYPE_S=Parent, COMMENT_T=comment3, _version_=1439995636627800064, timestamp=Mon Jul 08 08:34:14 EDT 2013}
2.3) select from parent where COMMENT_T="ventilation test" and TYPE_S="Parent" OR parent inner join child1 on KEY_S=KEY_S where COMMENT_T = "ventilation test" and TYPE_S="Child1" OR parent inner join child1 on KEY_S=KEY_S where COMMENT_T = "ventilation" and TYPE_S="Child2"
a) - no results from parent althought it should
{!join from=KEY_S to=KEY_S fromIndex=child1}(COMMENT_T:("ventilation test") AND TYPE_S:Child1) OR _query_:"{!join from=KEY_S to=KEY_S fromIndex=child2}(COMMENT_T:ventilation AND TYPE_S:Child2)" OR _query_:"COMMENT_T:(ventilation test) AND TYPE_S:Parent"
JoinQuery({!join from=KEY_S to=KEY_S fromIndex=child1}(+COMMENT_T:"ventil test" +TYPE_S:Child1) {!join from=KEY_S to=KEY_S fromIndex=child2}+COMMENT_T:ventil +TYPE_S:Child2 (+(COMMENT_T:ventil COMMENT_T:test) +TYPE_S:Parent))
b) - parser errors
_query_:"COMMENT_T:(ventilation test) AND TYPE_S:Parent" OR {!join from=KEY_S to=KEY_S fromIndex=child1}(COMMENT_T:("some text") AND TYPE_S:Child1) OR _query_:"{!join from=KEY_S to=KEY_S fromIndex=child2}(COMMENT_T:ventilation AND TYPE_S:Child2)"
c) - all expected results (from parent as well)
_query_:"COMMENT_T:(ventilation test) AND TYPE_S:Parent" OR _query_:"{!join from=KEY_S to=KEY_S fromIndex=child1}(COMMENT_T:(ventilation test) AND TYPE_S:Child1)" OR _query_:"{!join from=KEY_S to=KEY_S fromIndex=child2}(COMMENT_T:ventilation AND TYPE_S:Child2)"
(+(COMMENT_T:ventil COMMENT_T:test) +TYPE_S:Parent) JoinQuery({!join from=KEY_S to=KEY_S fromIndex=child1}+(COMMENT_T:ventil COMMENT_T:test) +TYPE_S:Child1) JoinQuery({!join from=KEY_S to=KEY_S fromIndex=child2}+COMMENT_T:ventil +TYPE_S:Child2)
_query_:"{!join from=KEY_S to=KEY_S fromIndex=child1}(COMMENT_T:(ventilation test) AND TYPE_S:Child1)" OR _query_:"{!join from=KEY_S to=KEY_S fromIndex=child2}(COMMENT_T:ventilation AND TYPE_S:Child2)" OR _query_:"COMMENT_T:(ventilation test) AND TYPE_S:Parent"
JoinQuery({!join from=KEY_S to=KEY_S fromIndex=child1}+(COMMENT_T:ventil COMMENT_T:test) +TYPE_S:Child1) JoinQuery({!join from=KEY_S to=KEY_S fromIndex=child2}+COMMENT_T:ventil +TYPE_S:Child2) (+(COMMENT_T:ventil COMMENT_T:test) +TYPE_S:Parent)
Results:
{id=1, KEY_S=1, TYPE_S=Parent, COMMENT_T=ventilation test, _version_=1439995541507276800, timestamp=Mon Jul 08 08:32:43 EDT 2013}
{id=2, KEY_S=2, TYPE_S=Parent, COMMENT_T=comment2, _version_=1439995541529296896, timestamp=Mon Jul 08 08:32:43 EDT 2013}
{id=3, KEY_S=3, TYPE_S=Parent, COMMENT_T=comment3, _version_=1439995541534539776, timestamp=Mon Jul 08 08:32:43 EDT 2013}
As a conclusion, I can write any query I want with union of multiple join and union with documents taken from the parent index only if I use _query_ for each individual piece from the query (one _query_ when querying the parent and one _query_ for each different join query). Unfortunately when using _query_ I cannot write "exact match" queries because I cannot add the query string in " (quotes) (I already add quotes for the query: _query_:"<actual query>").
I would say that the scenarios 2.2 (a-c) and 2.3 (a-b) should work as well. If those are not bugs of the query parser what syntax shall I use?
Thanks in advance!
Mihaela
________________________________
From: Yonik Seeley <yo...@lucidworks.com>
To: "solr-user@lucene.apache.org" <so...@lucene.apache.org>; mihaela olteanu <mi...@yahoo.com>
Sent: Saturday, July 6, 2013 10:31 PM
Subject: Re: Solr 4.x union of cross-joins
On Sat, Jul 6, 2013 at 2:22 PM, mihaela olteanu <mi...@yahoo.com> wrote:
> Hello,
>
> I have 3 indices that form a hierarchy. Basically these were constructed from 3 tables: parent, child1 and child2 and between parent and children there is a one to many relationship.
> parent (id,name)
>
> child1(id,join_key,field1)
>
> child2(id,join_key,field2)
>
>
> "join_key" is the foreign key in each of the child tables
>
> I would like to perform searches like the following: "give me the parents whose name is x or whose child1.field1 is y or child2.field2 is z".
>
> I tried with a query like the following:
> #first attempt
> /parent/select?q=(name:x) OR ({!join from=join_key to=id fromIndex=child1}field1:y) OR ({!join from=join_key to=id fromIndex=child2}field2:z)
>
> but it didn't work.
Any idea why?
Try adding debugQuery=true to see if the query is parsed correctly.
Try executing a single join alone, like
q={!join from=join_key to=id fromIndex=child1}field1:y
to see if the results are as expected.
-Yonik
http://lucidworks.com
Re: Solr 4.x union of cross-joins
Posted by Yonik Seeley <yo...@lucidworks.com>.
On Sat, Jul 6, 2013 at 2:22 PM, mihaela olteanu <mi...@yahoo.com> wrote:
> Hello,
>
> I have 3 indices that form a hierarchy. Basically these were constructed from 3 tables: parent, child1 and child2 and between parent and children there is a one to many relationship.
> parent (id,name)
>
> child1(id,join_key,field1)
>
> child2(id,join_key,field2)
>
>
> "join_key" is the foreign key in each of the child tables
>
> I would like to perform searches like the following: "give me the parents whose name is x or whose child1.field1 is y or child2.field2 is z".
>
> I tried with a query like the following:
> #first attempt
> /parent/select?q=(name:x) OR ({!join from=join_key to=id fromIndex=child1}field1:y) OR ({!join from=join_key to=id fromIndex=child2}field2:z)
>
> but it didn't work.
Any idea why?
Try adding debugQuery=true to see if the query is parsed correctly.
Try executing a single join alone, like
q={!join from=join_key to=id fromIndex=child1}field1:y
to see if the results are as expected.
-Yonik
http://lucidworks.com
Re: Solr 4.x union of cross-joins
Posted by Walter Underwood <wu...@wunderwood.org>.
If you flatten this to make a single table with rows for each combination of parent, child1, and child2, the query will be simple and probably very fast.
You've said it makes more sense to have three tables, and that is true for a relational database. For a search engine, like Solr, it makes more sense to flatten the data.
wunder
On Jul 6, 2013, at 11:22 AM, mihaela olteanu wrote:
> Hello,
>
> I have 3 indices that form a hierarchy. Basically these were constructed from 3 tables: parent, child1 and child2 and between parent and children there is a one to many relationship.
> parent (id,name)
>
> child1(id,join_key,field1)
>
> child2(id,join_key,field2)
>
>
> "join_key" is the foreign key in each of the child tables
>
> I would like to perform searches like the following: "give me the parents whose name is x or whose child1.field1 is y or child2.field2 is z".
>
> I tried with a query like the following:
> #first attempt
> /parent/select?q=(name:x) OR ({!join from=join_key to=id fromIndex=child1}field1:y) OR ({!join from=join_key to=id fromIndex=child2}field2:z)
>
> but it didn't work.
>
> If I put all these documents in a single index and I add also a type field whose value would be "Parent" for documents that come from parent table, "Child1" and "Child2" for the documents that come from the table child1 and child2 respectively I can perform the following query to get the results that I want:
>
>
> /parent/select?q={!join from=join_key to=id}field1:y OR field2:z
>
> The most important thing about this query is that I want to have back as results only documents of type "Parent"
>
> With the previous query I could not capture though also the search of parents whose name equals "x", but if i do a small trick and add a "join_key" field in documents of type "Parent" whose value equals the value in "id" field then I could write something like:
>
> /parent/select?q={!join from=join_key to=id}field1:y OR field2:z OR name:x
>
>
> and had all the results that I wanted.
>
> But in my use case it makes more sense to have 3 different indices and not storing all the documents in the same index.
>
> Does anyone know if I can somehow enable the first query that I gave as example above? I would say that it would be great to have such a feature in the next versions of Solr 4.x
>
> Thanks,
> Mihaela