lucene-java-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Chris Lu" <chris...@gmail.com>
Subject Re: Searching for multiple criteria (accross 2 tables)
Date Fri, 15 Feb 2008 17:10:05 GMT
Hi, lmctndi,

You can liberate yourself from this complicated schema, and use some
SQLs to select out the content and put the data into one Lucene index.

You may need to avoid Hibernate Search here for your case, which limit
you to one way of organizing Lucene indexes. I do think this is where
DBSight comes to rescue (Note: I am developing it.), where you can
quickly and easily have one or several flexible index structures,
incremental indexing, time or other value based ranking, etc.

-- 
Chris Lu
-------------------------
Instant Scalable Full-Text Search On Any Database/Application
site: http://www.dbsight.net
demo: http://search.dbsight.com
Lucene Database Search in 3 minutes:
http://wiki.dbsight.com/index.php?title=Create_Lucene_Database_Search_in_3_minutes
DBSight customer, a shopping comparison site, (anonymous per request)
got 2.6 Million Euro funding!


On Fri, Feb 15, 2008 at 8:16 AM, lmctndi <tn_dinh@hotmail.com> wrote:
>
>  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
>
>

---------------------------------------------------------------------
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