Mayur Champaneria Mayur Champaneria - 3 months ago 7
MySQL Question

Solr Range Query Does not work in Proper way for some fields

I have added 2 fields weight_value & nofyears_1 in solr documents.

Schema for both fields are as below

<field name="weight_value" type="string" indexed="true" stored="true" />
<field name="nofyears_1" type="string" indexed="true" stored="true" />


Both are stored as string as it stores numeric values.

Problem here is Range query works perfectly on weight_value field but doesn't work on nofyears_1 field.

like this

weight_value:[40 TO 50] => Get all the results only between 40 TO 50


But

nofyears_1:[1 TO 10] => Doesn't get results between 1 TO 10, but it fetches the result of 1 & 10 only.


As well as in,

nofyears_1:[1 TO 5] => It fetches the result of 1, 5 & 15 only.


Why my range query doesn't work on this field?

Both the fields are string, No Multiple values

Answer

If you want range queries with numeric values, use a numeric field. The problem now is that you're using a string field, and the sort order for strings are:

1
10
11
...
2
20
...
40
41 
..
49
50

.. so if you're querying for [1 TO 10] in a string field, you'll get just the first two entries back (while 40 TO 50 will retrieve all values from 40 to 50 - if you had entries with 400 or 499 they'd also be included):

..
--
1
10
--
11
...

If you use a proper field type (integer / int in this case) for your data, the range query will work as you expect.

Remember that you'll have to reindex after changing the field type (which, depending on how your setup is, either involves changing the definition in schema.xml or through the managed schema API).

Comments