3

We have requirement where there are three fields user date, recorded date and encoded date. We use Solr for searching and we have requirement to retrieve records using condition if value exists for user date use it else check for recorded date and if it also doesn't contain value use encoded date and if all three fields contains no value ignore that field.

This requirement is similar to COALESCE functionality in oracle where It accepts two or more parameters and returns the first non-null value in a list. If all parameters contain null values, it returns null.

Update :: We are using Solr 3.5.0

For example below is my values in table 

 DocumentID   User entered date      Recorded date      Encoded date
    A123        14th April 2016     12th April 2016 
    A124        12th April 2016                         11th April 2016
    A125                            13th April 2016     12th April 2016
    A126                            12th April 2016 
    A127                                                15th April 2016

If we search for April 12th 2016 then Solr should return me output as A124, A126 and not A123 or A125 because we have user entered date and recorded date for those fields respectively.

rajadilipkolli
  • 3,475
  • 2
  • 26
  • 49
  • I think you need to tackle this at your end once your get the data by applying some logic... – Abhijit Bashetti Apr 21 '16 at 09:08
  • it can be implemented with a simple query using boolean conditions OR – Oyeme Apr 21 '16 at 09:36
  • @Oyeme I tried OR condition but if there are values for both user date and recorded date. SOLR is searching in both fields which is not i am looking for. – rajadilipkolli Apr 21 '16 at 10:04
  • @AbhijitBashetti I was looking for that such logic. – rajadilipkolli Apr 21 '16 at 10:04
  • @rajadilipkolli you can use DataImportHanlder to write your own sql with all conditions and solr will index it :) – Oyeme Apr 21 '16 at 10:10
  • @Oyeme I have updated question with my clear requirement, we have index running and don't want to do full indexing again and only recently we have introduced user entered date so i think we should be looking beyond **DataImportHandler** – rajadilipkolli Apr 21 '16 at 10:33
  • You can try to use functions someting like {!func}exists(Field,1,0) http://wiki.apache.org/solr/FunctionQuery#if – Oyeme Apr 21 '16 at 12:32
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/109820/discussion-between-rajadilipkolli-and-oyeme). – rajadilipkolli Apr 21 '16 at 13:41

3 Answers3

1

Your best option is probably to copy the fields into a separate field in the order you want and then only keep the first value. In the latest Solr, I believe this can be done in the UpdateRequestProcessor step with CloneField and then FirstFieldValue processors. However, I am quite sure, these URPs were introduced in 5.x, and so are not available in Solr 3.5.

I suspect you would really have to apply this domain logic in the data before it is sent to Solr.

Alexandre Rafalovitch
  • 9,709
  • 1
  • 24
  • 27
  • Alex i agree with you but the problem is recorded date and encoded date are existing fields and we have added user date recently. so changing logic is also a problem and if you see table structure, it may contain null values and values can be added anytime – rajadilipkolli Apr 26 '16 at 09:06
  • You mean that you have already records indexed like that and reindexing is not an option? You are looking for pure query-time solution? – Alexandre Rafalovitch Apr 26 '16 at 09:11
  • Yeah pure query time solution in SOLR 3.5 – rajadilipkolli Apr 27 '16 at 11:30
0

Did you try with specifying field names to query with. something like this 

q=foo&qf=title,description

Did you try this

First query with only first field, user entered date. fetch all DocumentID. second, query with(same q value) something like if user field value is null and then search in second field value , fetch DocumentsID. Do the same for third field also. finally merge all DocumentsID.

Vinod
  • 1,965
  • 1
  • 9
  • 18
  • I tried this but it wont solve the problem mentioned in the question – rajadilipkolli Apr 22 '16 at 08:36
  • ok, query with only first field, user entered date. fetch all DocumentID. second, query with(same q value) something like if user field value is null and then search in second field value , fetch DocumentsID. Do the same for third field also. finally merge all DocumentsID. – Vinod Apr 22 '16 at 09:40
  • This approach could be a solution but hitting thrice is performance oriented, so can't be done in that way – rajadilipkolli Apr 29 '16 at 18:58
-1

It looks like you're looking for if(expression,trueValue,falseValue) function described in FunctionQuery

For example, for dismax query parser you can define boost function like

bf="recip(rord(if(exists(u_date),u_date,if(exists(r_date),r_date),if(exists(e_date),e_date,0))),1,1000,1000)"
Andrew Kolpakov
  • 429
  • 3
  • 8
  • [FunctionQuery](https://wiki.apache.org/solr/FunctionQuery#if) is introduced in Solr 4.0 but the version which we are using is Solr 3.5 – rajadilipkolli Apr 29 '16 at 19:37