CODEWITHSUNDEEP
solr
JP.
JP.

Reputation: 5606

SOLR - Match range query only if all dates in range are matched

I am using SOLR and storing an array of dates a salesperson is available to visit clients (trips can last anywhere from a day upwards, depending on the client request). For each salesperson I have a list of dates that they are available for the salesperson for a given month. There are other fields, including salesperson data, geolocation information, etc.

I am familiar with range queries but it seems that SOLRs range searches on arrays work differently than I would like - as long as any item in the array is a match then the range is a match). I would like to send SOLR a query with a range and only return a match if all dates in that range are found in the array. For example:

<arr name="available_dates">
    <date>2012-04-30T00:00:00Z</date>
    <date>2012-05-01T00:00:00Z</date>
    <date>2012-05-02T00:00:00Z</date>
</arr>

-- should match --
available_dates:[2012-04-30T00:00:00.000Z TO 2012-05-02T00:00:00.000Z]

-- should not match as 2012-04-29 is not contained in available_dates --
available_dates:[2012-04-29T00:00:00.000Z TO 2012-05-02T00:00:00.000Z]

Is this possible or am I going about this all wrong?

Upvotes: 1

Views: 1484

Answers (3)

MrGomez
MrGomez

Reputation: 23886

You have the right idea, but your initial query is a search instead of a match. Intuitively, your search within available_dates:[2012-04-30T00:00:00.000Z TO 2012-05-02T00:00:00.000Z] should contain all of the elements of available_dates for it to have matched successfully.

You have two options to implement this logic efficiently and successfully. You can either manually or dynamically perform the range query for each element in your array, or you can set up an ancillary that attempts to perform the match after your search has been performed. For example:

available_dates:[2012-04-30T00:00:00.000Z TO 2012-05-02T00:00:00.000Z](available_dates)

Which is saying, in left to right order: evaluate the range search, then check that all of the results from available_dates are contained in this evaluation (by way of a default AND query). If they are, return the element. If not, don't.

Syntactically, the above is untested and probably does not work. But procedurally, you should be able to draft the right query around this to fit your needs.

(Additional resource discussing the default AND behavior of composite search queries)

Upvotes: 1

Joyce
Joyce

Reputation: 1451

Assuming you're importing this data from database.

In your database or in your search index, create a new column that stores the max of your sales person's date (as in latest date), as well as a min. Also, calculate and store the difference between the max & min date.

Three criterias must be matched for a matching query (so use AND in the query)

  1. the differnce between the query's max & min can't be bigger than the difference as stored in the index

  2. you'd make sure {!frange l=0 u=difn_bet_query_max_and_min}sub(field_min,query_min)

  3. formulate the same thing for your max values

For a reference on function ranges http://www.lucidimagination.com/blog/2009/07/06/ranges-over-functions-in-solr-14/

Upvotes: 0

cberner
cberner

Reputation: 3040

Instead of using a range query you should use multiple clauses, one for each date.

So instead of available_dates:[2012-04-29T00:00:00.000Z TO 2012-05-02T00:00:00.000Z]

You should use available_dates:"2012-04-29T00:00:00.000Z" AND available_dates:"2012-04-30T00:00:00Z" AND available_dates:"2012-05-01T00:00:00.000Z" AND available_dates:"2012-05-02T00:00:00.000Z"

Hope that answers your question!

Upvotes: 0

Related Questions