lucene-java-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From lmctndi <tn_d...@hotmail.com>
Subject Searching for multiple criteria (accross 2 tables)
Date Fri, 15 Feb 2008 16:16:55 GMT

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: java-user-unsubscribe@lucene.apache.org
For additional commands, e-mail: java-user-help@lucene.apache.org


Mime
View raw message