Hi all, [ Sorry for the cryptic title but I can't think of a better one]
I am trying to integrate Lucene as a search engine for my database and would appreciate some help on a problem. First, some info about the development platform, I am developing on Windows using Hibernate for DB transactions (HSQL) with MySQL as a DB server and am using Hibernate search (which in turn uses Lucene) to perform searches. A typical database for the data would be something like this: Id Fname Lname Age Country ---------------------------------------- 1 John Smith 30 USA 2 John Doe 20 France 3 Jane Doe 40 USA Thus, each row represents one person and each column has data related to this person. If I want to search for a "John" living in the "USA", it's a straight forward "+Fname:John +Country:USA" However, my database is implemented differently and I have very little flexibility to change its implementation so I have to work with what is there. Here's a sample of the database: Data Table Id Name Value -------------------------------- 1 Fname John 1 Lname Smith 1 Age 30 1 Country USA 2 Fname John 2 Lname Doe 2 Age 20 2 Country France 3 Fname Jane 3 Lname Doe 3 Age 40 3 Country USA Now, all rows in Data Table with the same Id represent one IdCard and each row represents a name-value pair representing the data of each IdCard IdCard Table Id SIN ------------------------- 1 111-111-111 2 222-222-222 3 333-333-333 With this, the simple search above becomes quite complex. Since I have to work with this current database schema, how do I go about querying for a "John" living in the "USA" and getting the "IdCard" for such a query? I tried various methods without success 1. Name-Value (AND) search --> "+Name:Fname +Value:John" will return 2 hits (John Doe and John Smith) 2. Double Criteria search --> "+Value:John +Value:USA" won't work because "value" cannot match 2 different terms 3. "OR" search --> "Value:John Value:USA" will return 4 hits, 2 for John (1 for John Doe and 1 for John Smith) and 2 for USA (1 for John Smith and 1 for Jane Doe) 4. BooleanQuery and QueryFilters can't help because it basically the same as a boolean search (with caching and performance enhancements) I simply need to search the Data Table for various criteria and return the corresponding IdCard object. A simple solution can be an equivalent to the SQL "Where" clause. I can then do a search on value=John and value=USA where both IDs are the same. This would then return all the Johns who live n the USA with matching Ids only. I can then use this Id to look up the IdCard table to obtain the card for that person. Does Lucene supprt such a feature? If it is of any help, I can make minor changes to the DB schema (like adding a row for control purposes, etc.) but I cannot rewrite it totally. Any help/suggestion is greatly appreciated. Thanks. -- View this message in context: http://www.nabble.com/Searching-for-multiple-criteria-%28accross-2-tables%29-tp15502657p15502657.html Sent from the Lucene - Java Users mailing list archive at Nabble.com. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
