solr: how to join three cores?

Question

I'm trying to query on a core based on conditions on two related cores but I dont get any results. Each separate join works fine.

How should I phrase a query to join based on two cores?

In SQL world this is called an inner join with three tables to get the intersection.

SQL counterpart:

  SELECT *
  FROM proddev
  INNER JOIN department ON HMProductDevelopmentDepartment==id
  INNER JOIN season ON HMProductDevelopmentSeason==id

I'm running solr-7.3.1. Query is executed in http://localhost:8983/solr/#/proddev/query

 {!join from=id fromIndex=season to=HMProductDevelopmentSeason}name:"8-2018" AND _query_  {!join from=id fromIndex=department to=HMProductDevelopmentDepartment}DepartmentNumber:6515

main table: proddev

{
    "id":"48352.32064.2208.65165",
    "name":["0439370D"],
    "HMProductDevelopmentDepartment":["48352.32064.61248.42035"],
    "HMProductDevelopmentSeason":["48352.32064.24988.33959"],
    "HMCandidateForFileSync":[" FALSE    "],
    "PublishedDate":[" 11/14/2017 11:29:25 AM    "],
...
 }

related core: season

{
    "id":"48352.32064.24988.33959",
    "name":["8-2018"],
    "_version_":1611866808030068736,
    "name_str":["8-2018"]}]
}

related core: department

 {
    "id":"48352.32064.61248.42035",
    "name":["448"],
    "DepartmentNumber":[" 6515"],
    "_version_":1611923411375751168,
    "name_str":["448"],
    "DepartmentNumber_str":[" 6515"]}]
}

debug response:

"responseHeader":{
"status":0,
"QTime":1,
"params":{
  "q":"{!join from=id fromIndex=season to=HMProductDevelopmentSeason}name:\"8-2018\" AND _query_  {!join from=id fromIndex=department to=HMProductDevelopmentDepartment}DepartmentNumber:6515",
  "debugQuery":"on",
  "_":"1537194871447"}},
"response":{"numFound":0,"start":0,"docs":[]
  },
  "debug":{
"rawquerystring":"{!join from=id fromIndex=season to=HMProductDevelopmentSeason}name:\"8-2018\" AND _query_  {!join from=id fromIndex=department to=HMProductDevelopmentDepartment}DepartmentNumber:6515",
"querystring":"{!join from=id fromIndex=season to=HMProductDevelopmentSeason}name:\"8-2018\" AND _query_  {!join from=id fromIndex=department to=HMProductDevelopmentDepartment}DepartmentNumber:6515",
"parsedquery":"JoinQuery({!join from=id to=HMProductDevelopmentSeason fromIndex=season}+name:\"8 2018\" +_text_:_query_ {!join from=id to=HMProductDevelopmentDepartment fromIndex=department}DepartmentNumber:6515)",
"parsedquery_toString":"{!join from=id to=HMProductDevelopmentSeason fromIndex=season}+name:\"8 2018\" +_text_:_query_ {!join from=id to=HMProductDevelopmentDepartment fromIndex=department}DepartmentNumber:6515",
"explain":{},

score 0 · Answer 1 · answered Sep 18 '18 at 07:10

0

If you want use nested query you should fix mistakes in your query. Fixed query:

{!join from=id fromIndex=season to=HMProductDevelopmentSeason}name:"8-2018" AND _query_:"{!join from=id fromIndex=department to=HMProductDevelopmentDepartment}DepartmentNumber:6515"

Or you can use filter query for second join.

Links:
https://lucidworks.com/2009/03/31/nested-queries-in-solr/

answered Sep 18 '18 at 07:10

mrgrechkinn

883
8
19

thanks! It works when I put the second join in the fq-field but not when I use a nested query. When I use a nested query I get the following response: { "responseHeader":{ ..."root-error-class","org.apache.solr.common.SolrException"], "msg":"undefined field: \"HMProductDevelopmentDepartment\"", "code":400}} – Jesper Sep 18 '18 at 07:43
new query: {!join from=id fromIndex=season to=HMProductDevelopmentSeason}name:"8-2018" AND _query_:"{!join from=id fromIndex=department to=HMProductDevelopmentDepartment}DepartmentNumber:6515" – Jesper Sep 18 '18 at 07:51
I think you don't need nested at all, you can see this post https://stackoverflow.com/questions/39916297/how-to-or-two-joins-in-solr – mrgrechkinn Sep 18 '18 at 07:54

solr: how to join three cores?

1 Answers1