Sorting and filtering on fluctuating multi-currency price data?

[Gregg Donovan <gr...@gmail.com>]

In our current search app, we have sorting and filtering based on item
prices. We'd like to extend this to support sorting and filtering in the
buyer's native currency with the items themselves listed in the seller's
native currency. E.g: as a buyer, if my native currency is the Euro, my
search of all items between 10 and 20 Euros would also find all items listed
in USD between 13.90 and 27.80, in CAD between 14.29 and 28.58, etc.

I wanted to run a few possible approaches by the list to see if we were on
the right track or not. Our index is updated every few minutes, but we only
update our currency conversions every few hours.

The easiest approach would be to update the documents with non-USD listings
every few hours with the USD-converted price. That will be fine, but if the
number of non-USD listings is large, this would be too expensive (i.e. large
parts of the index getting recreated frequently).

Another approach would be to use ExternalFileField and keep the price data,
normalized to USD, outside of the index. Every time the currency rates
changed, we would calculate new normalized prices for every document in the
index.

Still another approach would be to do the currency conversion at IndexReader
warmup time. We would index native price and currency code and create a
normalized currency field on the fly. This would be somewhat like
ExternalFileField in that it involved data from outside the index, but it
wouldn't need to be scoped to the parent SolrIndexReader, but could be
per-segment. Perhaps a custom poly-field could accomplish something like
this?

Has anyone dealt with this sort of problem? Do any of these approaches sound
more or less reasonable? Are we missing anything?

Thanks for the help!

Gregg Donovan
Technical Lead, Search
Etsy.com

Re: Sorting and filtering on fluctuating multi-currency price data?

[Chris Hostetter <ho...@fucit.org>]

: ExternalFileField can only be used for boosting. It is not a
: "first-class" field.

correct, but it can be used in a FunctionQuery, which means it can be 
filtered on (using "frange") and (on trunk) it can be sorted on, which are 
the two needs the OP asked about...

: > : Another approach would be to use ExternalFileField and keep the price data,
: > : normalized to USD, outside of the index. Every time the currency rates
: > : changed, we would calculate new normalized prices for every document in the
: > : index.

-Hoss

Re: Sorting and filtering on fluctuating multi-currency price data?

[Lance Norskog <go...@gmail.com>]

ExternalFileField can only be used for boosting. It is not a
"first-class" field.

On Thu, Oct 28, 2010 at 11:07 AM, Chris Hostetter
<ho...@fucit.org> wrote:
>
> : Another approach would be to use ExternalFileField and keep the price data,
> : normalized to USD, outside of the index. Every time the currency rates
> : changed, we would calculate new normalized prices for every document in the
> : index.
>
> ...that is the approach i would normally suggest.
>
> : Still another approach would be to do the currency conversion at IndexReader
> : warmup time. We would index native price and currency code and create a
> : normalized currency field on the fly. This would be somewhat like
> : ExternalFileField in that it involved data from outside the index, but it
> : wouldn't need to be scoped to the parent SolrIndexReader, but could be
> : per-segment. Perhaps a custom poly-field could accomplish something like
> : this?
>
> ...that would essentially be what ExternalFileFiled should start doing, it
> just hasn't had anyone bite the bullet to implement it yet -- if you wnat
> to tackle that, then i would suggest/request/encourage you to look at
> doing it as a patch to ExternalFileField that could be contributed back
> and reused by all.
>
> With all of that said: there has also been a recent contribution of a
> "MoneyFieldType" for dealing precisesly with multicurrency
> sorting/filtering issues that you should definitley take a look at...
>
> https://issues.apache.org/jira/browse/SOLR-2202
>
> -Hoss
>



-- 
Lance Norskog
goksron@gmail.com

Re: Sorting and filtering on fluctuating multi-currency price data?

[Chris Hostetter <ho...@fucit.org>]

: Another approach would be to use ExternalFileField and keep the price data,
: normalized to USD, outside of the index. Every time the currency rates
: changed, we would calculate new normalized prices for every document in the
: index.

...that is the approach i would normally suggest.

: Still another approach would be to do the currency conversion at IndexReader
: warmup time. We would index native price and currency code and create a
: normalized currency field on the fly. This would be somewhat like
: ExternalFileField in that it involved data from outside the index, but it
: wouldn't need to be scoped to the parent SolrIndexReader, but could be
: per-segment. Perhaps a custom poly-field could accomplish something like
: this?

...that would essentially be what ExternalFileFiled should start doing, it 
just hasn't had anyone bite the bullet to implement it yet -- if you wnat 
to tackle that, then i would suggest/request/encourage you to look at 
doing it as a patch to ExternalFileField that could be contributed back 
and reused by all.

With all of that said: there has also been a recent contribution of a 
"MoneyFieldType" for dealing precisesly with multicurrency 
sorting/filtering issues that you should definitley take a look at...

https://issues.apache.org/jira/browse/SOLR-2202

-Hoss