in sql we can write query like
select item.id from item table where max(item.price) < 100
I would like to know how to specify the max(item.price) when using index.
is there any lucene or Hibernate search based qury we can use , i mean using Range filters etc.
Thanks,
indra.

Hi,

RangeQuery or RangeFilter are both valid solutions to the problem. The latter might be the better option if the range is quite big. Remember Lucene is text based that means there is no <100. Internally the range will have to be expanded into its explicit values.
It is important also to make sure that you use the right padding for the price you are going to index. All the price fields need to be the same length and padded with leading '0's. Have a look at the PaddedIntegerBridge.

--Hardy

But Range filer/qurey only gets data with in a range. But I want to get a single result that is less than or nearer to the price i mentioned. I want to use a thing like aggregate function MAX . is it possible? If i dont have the max function I have to sort the result using HS Sort object and find the top one, which i want to avoid.I understood the Padding part.

Thanks for the reply.

I must admit I don't quite understand what you try to achieve with the SQL query from your original post.
That said, there is no max() function for Lucene. Lucene is a fulltext search engine. If you really depend on the max() function you might be using the wrong tool.

Alternatively you could run a HQL first to determine the max(item.price) and then use this value for your fulltext query. Whether this a good idea or not depends on the usecase. Personally I would avoid it.

It is hard to come up with any better suggestion without a better knowledge of your domain model and how you try to map it into the Lucene index.

--Hardy

Actually my requirement is for the price of an item with the nearest time i specified. And I have multiple conditions satisfy.

I have an index of price data of different instruments(scripts) with the following index fields.
id, name, price, type, exchange time.
ex: 1, Microsoft, 120$, 1, NYSE, 200909132626

And I want to qurey for price by using the following parameters:
id: Instrument id
time Date and time that a price is sought for
type Type of price sought: 1= Opening, 2= closing etc
exchange The exchange that a price is sought for.

The following conditions need to be handled:
1. If a price exists at the exact date – time that is requested then return that price.
2. if not return the instrument whose timestamp is closest to the requested time.
4. If no price exists for the type, return the opening price
5. else previous day closing
6. else return null.

I tried something like getting data with: time:[day begining time stamp TO requesed time stamp]. sort(). getMaxresult(1).list() ;

I wanted to remove the sort part. And also wanted to put all conditions in a single query.
How I can approach this with best performence.

Hi,

if this are you exact requirements, then I am not sure why you are using Hibernate Search. Where is the fulltext search part. For the table and problem described I would stick to HQL or JPA-QL.

Whether you can get what you want in one query is another problem. Probably not.

--Hardy