Re: DIH : SQL query (sub-entity) is executed although variable is not set (null or empty list)

[MitchK]

Hi Chantal,

instead of:

<entity name="prog" ...> 
        <field name="vip" ...> /* multivalued, not required */ 
        <entity name="ssc_entry" dataSource="ssc" onError="continue" 
                query="select SSC_VALUE from SSC_VALUE 
                        where SSC_ATTRIBUTE_ID=1 
                          and SSC_VALUE in (${prog.vip})"> 
                <field column="SSC_VALUE" name="vip_ssc" /> 
        </entity> 
</entity> 

you do:

<entity name="prog" ...> 
        <field name="vip" ...> /* multivalued, not required */ 
        <entity name="ssc_entry" dataSource="ssc" onError="continue" 
                query="${yourCustomFunctionToReturnAQueryString(prog.vip,
..., ...)}"> 
                <field column="SSC_VALUE" name="vip_ssc" /> 
        </entity> 
</entity> 

The yourCustomFunctionToReturnAQueryString(vip, querystring1, querystring2)
{
    if(vip != null && !vip.equals(""))
    {
         StringBuilder sb = new StringBuilder(50);
         sb.append(querystring1); // SELECT SSC_VALUE from SSC_VALUE where
SSC_ATTRIBUTE_ID=1 
                                               and SSC_VALUE in (
         sb.append(vip);//VIP-value
         sb.append(querystring2);//just the closing ")"
         return sb.toString();
     }
     else
     {
            return "SELECT \"\" AS yourFieldName";
     }
}

I expect that this method is called for every vip-value, if there is one.

Solr DIH uses the returned querystring to query the database. So, if
vip-value is empty or null, you can use a different query that is blazing
fast (i.e. SELECT "" AS yourFieldName - just an example to show the logic).
This query should return a row with an empty string. So Solr fills the
current field with an empty string.

I don't know how to prevent Solr from calling your ssc_entry-entity, when
vip is null or empty.
But this would be a solution to handle empty vip-strings as efficient as
possible. 



> If realized 
> that I have to throw an exception and add the onError attribute to the 
> entity to make that work. 
> 
I am curious:
Can you show how to make a method throwing an exception that is accepted by
the onError-attribute?

I hope we do not talk past eachother here. :-)

Kind regards,
- Mitch
-- 
View this message in context: 
http://lucene.472066.n3.nabble.com/DIH-SQL-query-sub-entity-is-executed-although-variable-is-not-set-null-or-empty-list-tp995983p998950.html
Sent from the Solr - User mailing list archive at Nabble.com.