lucene-java-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Erick Erickson" <erickerick...@gmail.com>
Subject Re: Searching for multiple criteria (accross 2 tables)
Date Fri, 15 Feb 2008 17:55:26 GMT
To expand a bit on Chris's first point: Take off your DB hat and put on
your search hat <G>. It sounds like you have simply moved your database
tables into Lucene and want to search across them. My rule is that
whenever you find yourself trying to make Lucene act like a DB, you
need to pause and reflect on your design.....

So, from your example, you select all the data relating to id 1 from
*all* your tables, and index that as a single document in Lucene. Very
simplistically, your document for ID 1 has the fields
Fname, Lname, Age, Country, and SIN.

Your query is now very simple,
+Fname:john +County:USA

and to get the related SIN, you iterate over your hits
and extract the SIN from each hit.

If I understand your problem, that is <G>.

In general, the strategy is to de-normalize your information
when you build your index....

Best
Erick

On Fri, Feb 15, 2008 at 11: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
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message