Indeed , this worked .
The fix that was required was related to the how the document is
represented. It depends on the unique key. For the same unique key, it
will always update the existing document. So to avoid it, I used the
oracle sequence to identify the record, it can be oracle row number or
if none are required, it can be UUID field from SOLR.
Now Im able to search for gradename:MBA AND grade:B with the relation
maintained.
The updated dataconfig is shown below
<dataConfig>
<dataSource driver="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@//localhost:1521/XE" user="test"
password="test" />
<document>
<entity name="employee" query="select employee_seq.nextval as
someid ,a.empid, empname, gradeid, gradename, grade from employee a
left outer join qualification b on a.empid = b.empid">
</entity>
</document>
</dataConfig>
Schema
<field name="someid" type="integer" indexed="true" stored="true"
required="true" multiValued="false" />
<field name="EMPID" type="integer" indexed="true" stored="true"
required="true" multiValued="false" />
<field name="empname" type="string" indexed="true" stored="true" />
<field name="gradeid" type="integer" indexed="true" stored="true" />
<field name="gradename" type="string" indexed="true" stored="true" />
<field name="grade" type="string" indexed="true" stored="true" />
<uniqueKey>someid</uniqueKey>
On Sat, Oct 27, 2012 at 10:49 AM, v vijith <vvij...@gmail.com> wrote:
> Hi,
>
> If I write a query like this, is there a way I can achive the results
> that I need
>
> select * from employee a left outer join qualification b on a.empid = b.empid;
>
> This will return 5 records, 1 per employee qualification. Can this be
> indexed as is?
>
> 1, John, MBA, A
> 1, John, Lead, B
> 2, George, MBA, B
> 2, George, PM, C
> 3, Viktor, null, null
>
> How do I put the schema so that this can be searched as is?
>
> I would not prefer to put database specific functions in my solution.
>
> On Sat, Oct 27, 2012 at 7:08 AM, Gora Mohanty <g...@mimirtech.com> wrote:
>> On 27 October 2012 01:20, v vijith <vvij...@gmail.com> wrote:
>> [...]
>>> The dataconfig file is
>>> <document>
>>> <entity name="employee" query="select * from employee">
>>> <entity name="qualification" query="select * from
>>> qualification where empid='${employee.EMPID}'"/>
>>> </entity>
>>> </document>
>> [...]
>>
>> The SELECT in the nested entity "qualification" should fetch
>> all qualifications for the given employee. How to do that is
>> database dependent, e.g., one would use something like
>> group_concat() in mysql. After collecting multiple qualifications
>> in a single string, one can use a transformer to break the
>> string at the separator used in group_concat(), and populate
>> the desired Solr field with the pieces.
>>
>> Depending on your expertise, it might be easier to do this
>> through a Solr XML document, or SolrJ.
>>
>> Regards,
>> Gora
