3

I'm struggling with SOLR and SQL Data Import. I need to index data coming from 2 tables the first one is "masters", the second one is "details". The relation among these two tables is 1-to-n and it is enforced by colum m_id present on both tables:

CREATE TABLE "masters" (m_id             NUMBER(10), 
                        m_code           VARCHAR2(100 CHAR), 
                        m_description    VARCHAR2(1000 CHAR),
                        PRIMARY KEY (m_id));

CREATE TABLE "details" (d_id             NUMBER(10), 
                        s_code           VARCHAR2(100 CHAR), 
                        s_description    VARCHAR2(1000 CHAR), 
                        m_id             NUMBER(10), 
                        PRIMARY KEY (d_id),
                        CONSTRAINT fk_details_masters FOREIGN KEY (m_id) REFERENCES "masters"(m_id));

I would like to have one indexed document for each row present in "masters" table, every documents should contain the ARRAY of the "details" table.

The outcome I prefigured in my mind is something like this:

master = {
 "ID": "1"
 "m_code": "master53",
 "m_description": "John Doe",
 "details": [ 
              {
               "d_code": "detail001",
               "d_description": "Shirts"
              },
              {
               "d_code": "detail002",
               "d_description": "Shoes"
              },
              {
               "d_code": "detail003",
               "d_description": "hats"
              }
            ]
}

But I'm only able to produce something like this:

master = {
 "ID": "1",
 "m_code": "master53",
 "m_description": "John Doe",
 "d_code": ["detail001","detail002","detail003"],
 "d_description": ["Shirts","Shoes","hats"]
}

using a configuration like this:

<dataConfig>
  <dataSource type="JdbcDataSource" 
              driver="oracle.jdbc.driver.OracleDriver"
              url="jdbc:oracle:thin:@//dora64svil.icc.crifnet.com:1521/cbrisvil.dbcrif.net"
              user="CBBTC" 
              password="w4.gJ6Qf1p7Z_i4qFp3W_d"/>
  <document>
    <entity name="master" rootEntity="True"
            query="SELECT m_id, m_code, m_description from master">
        <field column="m_id" name="ID"/>
        <field column="m_code" name="m_code_s"/>
        <field column="m_description" name="m_description_s"/>
        <entity name="details"  rootEntity="False"
                query="SELECT d_code, d_description from details where = m_id '${master.m_id}'">
            <field column="d_code" name="d_code_ss"/>
            <field column="d_description" name="d_description_ss"/>
        </entity>
    </entity>
  </document>
</dataConfig>

I played a lot with configuration and with schema.xml field definitions but I was not able to find out the proper way. (the field suffixes "_s" and "_ss" are present just to trigger the proper field types into schema)

I think what I'm trying to do is to give a "name" to the nested entity, where is the error? Is what I'm trying to do wrong in concept?

MonDeveloper
  • 186
  • 9

2 Answers2

0

You have to set the field as multiValued=true in schema.xml

Jose
  • 31
  • 2
0

Solr does not support retrieving nested data as of today.

Your best bet is to use fq=_root_:<your_id> and result grouping.

To acheive result grouping you'll need to add a field, say type_s and assign it the value 'master' or 'details', respectively using the TemplateTransformer.

The output you'll achieve will be like,

"groupValue":master,
doc: {
 "ID": "1"
 "m_code": "master53",
 "m_description": "John Doe",
}

"groupValue":"details",
 "docs":  
              {
               "d_code": "detail001",
               "d_description": "Shirts"
              },
              {
               "d_code": "detail002",
               "d_description": "Shoes"
              },
              {
               "d_code": "detail003",
               "d_description": "hats"
              }
Razen
  • 156
  • 8