How to store a value pair with Lucene

Question

I have a database which contains a Store, a Product, and a LocationInStore. There can be many stores, each having a subset of products, and each product can have a location in each store.

For example, in Store 1, location of Product 1 can be Aisle 1, while in Store 2 location of Product 1 can be Floor 2, Aisle 4. Location is an arbitrary String.

The tables (in quotes) are as such:

'Store' columns: store_id (int)
'Product' columns: product_id (int), name (String), price (String)
'Location' (a join table) columns: store_id (int), product_id (int), location (String) // I know I could just add a different table table but this suffices for now.

Getting the location for a product is quite simple using MySQL, but if I want to do so with Lucene, its a little tougher.

I was thinking of adding the fields to a Lucene Index like this:

Document doc = new Document();
doc.add(new TextField("productName", productName, Field.Store.YES));
doc.add(new FloatField("price", price, Field.Store.YES));
doc.add(new TextField("store_numbers", allStoreNumbersAsString, Field.Store.YES)); // this way I can look up a single store number
doc.add(new TextField("location", location, Field.Store.YES));

But the problem is if I search based on store number I would still get multiple locations as they are part of a different field.

Or maybe I should do something like:

Document doc = new Document();
doc.add(new TextField("productName", productName, Field.Store.YES));
doc.add(new FloatField("price", price, Field.Store.YES));
doc.add(new TextField("store_numbers", allStoreNumbersAsString, Field.Store.YES)); // this way I can look up a single store number
String[] stores = allStoreNumbersAsString.split(",");
for(int i=0; i<stores.length; i++){
    doc.add(new TextField("store_location", stores[i]+ ", " + location, Field.Store.YES));
}

What I'm looking for is to store a (store number, location in store) value pair in the index, but in a way the I can query by store number and then get the location in the store that corresponds to that product.

What's the best way to add these fields to achieve this?

Guy Gavriely Guy Gavriely · Accepted Answer · 2014-01-17T20:53:32

every store should be saved in a different document, so you end up doing something like:

for(Product product: products) {
    for(Store store: product.stores) {
        Document doc = new Document();
        doc.add(new TextField("productName", product.name, Field.Store.YES));
        doc.add(new FloatField("price", price, Field.Store.YES));
        doc.add(new TextField("store", store, Field.Store.YES));
        doc.add(new TextField("location", location, Field.Store.YES));
    }
}

How to store a value pair with Lucene

1 Answers