SOLR - How to handle AND clause between 2 multivalue fields

Question

How would we use AND clause in the SOLR query between 2 fields those are multiple value fields?

For example; following is the picture of database table

JobseekerID CompanyID Email Sent On
=========== =========== ==============

123 ABC 22-Jul-2011

123 XYZ 01-Jul-2011

Now in Sql, if i query

Where CompanyID = "ABC" AND EmailSentOn > 14-Jul-2011 (It will return TRUE)

Where CompanyID = "XYZ" AND EmailSentOn > 14-Jul-2011 (It will return FALSE)

How would we handle it in SOLR? Following is the picture of my SOLR Collection, how i am storing the data

{int name="jobseekerid"}123{/int}

.....................

{arr name="CompanyID"}

   {str}ABC{/str}

   {str}XYZ{/str}

{/arr}

{arr name="EmailSentOn"}

   {str}2011-07-22T17:44:00Z{/str}

   {str}2011-07-01T05:10:00Z{/str}

{/arr}

How can i query in the SOLR Now for Jobseeker 123. Because for SOLR there is not any relation between CompanyID and EmailSentON. It takes them two separate entities.

When we will query Company ID is "ABC" and EmailSentOn >= 14-July-2011 it will return TRUE and when we will say CompanyID is "XYZ" and EmailSentOn >= 14-July-2011 it will again return TRUE because SOLR will see, does company ID "XYZ" exist and any date in the 'email sent on' is greater than 14-July then it will return true.

But we need it to check if the CompanyID "XYZ" then the company XYZ date is greater than 14-july or not. Which is not, because XYZ sent email date is "01-Jul". So it should return false for that.

I hope you get my point. Can anyone please guide me how would we handle this in SOLR?

LAW LAW · Accepted Answer · 2011-07-24T16:50:23

The problem here is with the definition of the schema - you don't actually have multiValued data! Instead, you should just have two separate indexed documents with single values for CompanyId and EmailSentOn. Then when you search by date you get back just the contents of one document or another and don't have the results jumbled as you do now.

SOLR - How to handle AND clause between 2 multivalue fields

1 Answers