2

I have a Solr schema where my objects can have these two fields:

  • SerialNumberStart
  • SerialNumberEnd

For example:

"SerialNumberStart": "0000067098910",
"SerialNumberEnd": "0000067098920",

When querying, I want to be able to pass in a SerialNumberStart in my query. If that serialNumber matches on a document's serialNumberStart, I want to grab that document. However, when it doesn't match, I want to find all documents where the serialNumber I pass in is between SerialNumberStart and SerialNumberEnd. The second part of my query after the or should be something like: get the documents where someSerialStart is between documentSerialStart and documentSerialEnd.

I build up the query from JavaScript. I was trying to put the range together as follows, but it doesn't seem to be correct, yet I don't know how to fix it.

var value = req.query['matchedPattern.serialNumber'];
return 'SerialSNumberStart:' + value +  ' OR (SerialNumberStart:[* TO ' + value + '] AND SerialNumberEnd:[' + value + ' TO *])';

Thanks.

Joris Ooms
  • 11,880
  • 17
  • 67
  • 124
  • Are u getting wrong results? Or are you getting an error? Have you tried querying solr directly instead of forming the query inside javascript? If yes does that query give result? – jay Oct 08 '16 at 00:14
  • @jay I am getting no results with my query. Please read my comments on the answers below; I pretty much need help with actually building the query to do what I want. Nevermind the JS part actually.. – Joris Ooms Oct 08 '16 at 11:55

2 Answers2

1

I updated the answer based on your clarifications. There are 4 cases you need to investigate: 1 SN exists 2 SN does not exist 2.1 Get range before SN 2.2 Get range after SN

Intuitively, I'd definitely break it into two blocks: 1 SN exists, so serve it back to the client. Here is what it looks like:

http://solrserver.us-west-2.compute.amazonaws.com:8983/solr/hellosolr/select?indent=on&q=id:S9V7464-APL-KIT3&wt=json

The response is like:

{
"responseHeader":{
"status":0,
"QTime":0,
"params":{
"q":"id:S9V7464-APL-KIT3",
"indent":"on",
"wt":"json"}},
"response":{"numFound":1,"start":0,"docs":[
{
"id":"S9V7464-APL-KIT3",
...

  1. If your SN does not exist, the response returns "numFound":1, this is when you need to run your searches. So if there is no doc, the query would look the following:

    http://solrserver.us-west-2.compute.amazonaws.com:8983/solr/hellosolr/select?indent=on&q=id:S9V7464-APL-KIT5&rows=1&sort=id%20asc&start=0&wt=json

The response looks like:

{
"responseHeader":{
"status":0,
"QTime":0,
"params":{
"q":"id:S9V7464-APL-KIT5",
"indent":"on",
"start":"0",
"sort":"id asc",
"rows":"1",
"wt":"json"}},
"response":{"numFound":0,"start":0,"docs":[]
}}

2.1 So you need the next neighbour before the SN. There are two things I'd add to the descending range search, sorting and a limit to the number of answers. Here is what the query would like:

http://solrserver.us-west-2.compute.amazonaws.com:8983/solr/hellosolr/select?indent=on&q=id:[*%20TO%20S9V7464-APL-KIT5]&rows=1&sort=id%20desc&start=0&wt=json

The response would look like:

{
"responseHeader":{
"status":0,
"QTime":0,
"params":{
"q":"id:[* TO S9V7464-APL-KIT5]",
"indent":"on",
"start":"0",
"sort":"id desc",
"rows":"1",
"wt":"json"}},
"response":{"numFound":25,"start":0,"docs":[
{
"id":"S9V7464-APL-KIT3",
"name":["Belkin Mobile Power Cord for iPod w/ Dock"],
"manu":["Belkin"],
"manu_id_s":"belkin",
"cat":["electronics",
"connector"],
"features":["car power adapter, white"],
"weight":[6.7],
"price":[19.95],
"popularity":[1],
"inStock":[false],
"store":["45.18014,-93.87741"],
"manufacturedate_dt":"2005-08-01T16:30:25Z",
"_version_":1547654166135963648}]
}}

2.2 You need an ascending range, sorted and limited by the number of returned docs. Something like:

http://solrserver.us-west-2.compute.amazonaws.com:8983/solr/hellosolr/select?indent=on&q=id:[S9V7464-APL-KIT5%20TO%20*]&rows=1&sort=id%20asc&start=0&wt=json

The response would be similar to this:

{
"responseHeader":{
"status":0,
"QTime":0,
"params":{
"q":"id:[S9V7464-APL-KIT5 TO *]",
"indent":"on",
"start":"0",
"sort":"id asc",
"rows":"1",
"wt":"json"}},
"response":{"numFound":8,"start":0,"docs":[
{
"id":"S9V7464-APL-KIT7",
"name":["Belkin Mobile Power Cord for iPod w/ Dock"],
"manu":["Belkin"],
"manu_id_s":"belkin",
"cat":["electronics","connector"],
"features":["car power adapter, white"],
"weight":[6.7],
"price":[19.95],
"popularity":[1],
"inStock":[false],
"store":["45.18014,-93.87741"],
"manufacturedate_dt":"2005-08-01T16:30:25Z",
"_version_":1547654166137012224}]
}}

Increasing rows returns more documents, modifying start can be used as an offset to get the Nth neighbour.

Lefty G Balogh
  • 1,771
  • 3
  • 26
  • 40
  • Where I get stuck is.. I only have one value, right? The serialNumber from the document I'm searching for. The query that I run needs to return the document with that SN, or the documents where my value is between those documents' SNStart and SNEnd. I don't have a 5 or a 19, in my case? Wouldn't `300 TO 300` just return 300? – Joris Ooms Oct 08 '16 at 11:51
  • You got me all confused now. :) 300 TO 300 does not give you the right results, that is for sure. Let me paraphrase to see if I get your explanation right: If you have the entire serial number space of: 1,2,3,7,8,9; Then if you search for 3 - you want the doc whose serial number is 3; If you search for 5, you want the docs in the 2 ranges back: 1,2,3 AND 7,8,9. Is this what you are saying? – Lefty G Balogh Oct 08 '16 at 20:33
  • I think you are not getting results because you are using an AND operator - and there are no docs that would be in both sub-ranges. The only problem is that an OR would return the entire search space ie everything except for a non-existing doc. Or am I interpreting it wrong? – Lefty G Balogh Oct 08 '16 at 21:15
  • Haha, sorry. It's confusing me too, I have asked my client like five times what they want to achieve.. So if I query any document, it'll have both a `serialNumberStart` and a `serialNumberEnd`. If I search for a serialNumberStart and it finds a document matching that, I want to return that document. (Like your example with 3). If it doesn't find it where serialStart = 3, but there is, for example, a document with serialStart: 2 and serialEnd: 5, I want to return that document, because 3 is between that range (2->5). – Joris Ooms Oct 08 '16 at 21:50
  • Magic. I think I get it now and it does make sense. You probably just need to sort the responses and limit the number of docs. I updated the answer above accordingly. Check it out to see if it makes sense to you as well. – Lefty G Balogh Oct 09 '16 at 04:45
  • So, in the second case where the document can't be found, I would need to run two extra queries? Or can this be done in one query? With two queries I would need to somehow merge the results, right? I suppose this can be done in JS or whatever environment I'm working with. I'm at work now so I'll try this and get back to you. Thanks a lot for your time! – Joris Ooms Oct 11 '16 at 07:12
  • Correct. Though I don't know the specific business case, if I were the customer, I'd want to serve the exact match to my users straight off the bat, while I suspect the other two are more like: "Sorry we don't have the exact serial number, but here are two recommendations" type of use-case. My pleasure, let me know if you get stuck on other Solr issue, I'm glad to give it a lil more time. – Lefty G Balogh Oct 11 '16 at 18:40
0

You query seems to be opposite of what you what. You are finding records before SerialNumberStart and after SerialNumberEnd. Get the query right in Solr first and then map it to Javascript.

Also, you seem to not quote the value in the first clause. You may want to do so just in case.

Alexandre Rafalovitch
  • 9,709
  • 1
  • 24
  • 27
  • Maybe I formulated my question wrong. I'm actually stuck with building the correct query. I pass in one value, and I want either the doc with that SNStart value, or all the docs where my value is between their SNStart and SNEnd. And I'm not sure how to build that query, I'm not even sure if `TO` is the correct way to do this? – Joris Ooms Oct 08 '16 at 11:53