You are viewing a plain text version of this content. The canonical link for it is here.
Posted to java-user@lucene.apache.org by lmctndi <tn...@hotmail.com> on 2008/02/15 17:16:55 UTC
Searching for multiple criteria (accross 2 tables)
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
Re: Searching for multiple criteria (accross 2 tables)
Posted by Chris Lu <ch...@gmail.com>.
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...@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
Re: Searching for multiple criteria (accross 2 tables)
Posted by Jake Mannix <ja...@gmail.com>.
What the other posters are referring to is that you will have to
probably write some java code to do lucene indexing: you can get
access to your model objects (with all their dependent data) in java.
- since you are using hibernate, this shouild be easy- then create
lucene documents from your model objects and index! These documents
would be searchable in the way you want.
You will probably have to do some reading on how to write lucene
indexing code.
-jake
On 2/15/08, lmctndi <tn...@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
>
>
--
Sent from Gmail for mobile | mobile.google.com
---------------------------------------------------------------------
To unsubscribe, e-mail: java-user-unsubscribe@lucene.apache.org
For additional commands, e-mail: java-user-help@lucene.apache.org
Re: Searching for multiple criteria (accross 2 tables)
Posted by Emmanuel Bernard <em...@hibernate.org>.
Not sure if it's too late for you. But here are my comments if you
want to stick with Hibernate and Hibernate Search
Generally speaking, once you have the query to retrieve the data per
id, you can map this query to an entity in Hibernate using either:
- @Formula for simple cases
- @Loader for more complex cases
Once mapped as an entity, the mapping to Lucene via Hibernate Search
is business as usual.
Alternatively, you can use a class level @FieldBridge and map the
data the way you want in Lucene from an entity object. Note that I
don't think this strategy will suit your current needs.
Emmanuel
On Feb 15, 2008, at 15:24, Chris Lu wrote:
> Sorry, sent the previous draft email by mistake. Here is the
> correct one.
>
> Sounds a typical SQL pivot problem.
>
> select Id, SIN, data.*
> from IdCard, (SELECT
> ID
> MAX(CASE WHEN name = 'Fname' THEN Value END) AS Fname,
> MAX(CASE WHEN name = 'Lname' THEN Value END) AS Lname,
> MAX(CASE WHEN name = 'Age' THEN Value END) AS Age,
> MAX(CASE WHEN name = 'Country' THEN Value END) AS Country
> FROM
> DATA_Table
> GROUP BY
> ID
> ) data
>
> To speed things up, you can split the SQLs into 2 for better
> performance.
>
> This is how DBSight does this. You can write your own SQLs, but
> generally it's the same methods.
>
>
> --
> 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 11:27 AM, lmctndi <tn...@hotmail.com> wrote:
>>
>> Thanks for your reply.
>>
>> Your idea prompts more questions:
>>
>>
>> I understand what you are saying but don't know how to implement
>> it. How do
>> you go about joining all rows of all the tables belonging to one
>> person and
>>
>>
>> to index them so that I can actually use
>> "+Fname:john +County:USA" as a query?
>>
>>
>> Erick Erickson wrote:
>>>
>>> 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
>>>
>>
>> --
>> View this message in context: http://www.nabble.com/Searching-for-
>> multiple-criteria-%28accross-2-tables%29-tp15502657p15508362.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
>
---------------------------------------------------------------------
To unsubscribe, e-mail: java-user-unsubscribe@lucene.apache.org
For additional commands, e-mail: java-user-help@lucene.apache.org
Re: Searching for multiple criteria (accross 2 tables)
Posted by Chris Lu <ch...@gmail.com>.
Sorry, sent the previous draft email by mistake. Here is the correct one.
Sounds a typical SQL pivot problem.
select Id, SIN, data.*
from IdCard, (SELECT
ID
MAX(CASE WHEN name = 'Fname' THEN Value END) AS Fname,
MAX(CASE WHEN name = 'Lname' THEN Value END) AS Lname,
MAX(CASE WHEN name = 'Age' THEN Value END) AS Age,
MAX(CASE WHEN name = 'Country' THEN Value END) AS Country
FROM
DATA_Table
GROUP BY
ID
) data
To speed things up, you can split the SQLs into 2 for better performance.
This is how DBSight does this. You can write your own SQLs, but
generally it's the same methods.
--
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 11:27 AM, lmctndi <tn...@hotmail.com> wrote:
>
> Thanks for your reply.
>
> Your idea prompts more questions:
>
>
> I understand what you are saying but don't know how to implement it. How do
> you go about joining all rows of all the tables belonging to one person and
>
>
> to index them so that I can actually use
> "+Fname:john +County:USA" as a query?
>
>
> Erick Erickson wrote:
> >
> > 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
> >
>
> --
> View this message in context: http://www.nabble.com/Searching-for-multiple-criteria-%28accross-2-tables%29-tp15502657p15508362.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
Re: Searching for multiple criteria (accross 2 tables)
Posted by lmctndi <tn...@hotmail.com>.
Thanks for your reply.
Your ideas prompts more questions:
I understand what you are saying but don't know how to implement it. How do
you go about joining all rows of the tables belonging to one person and to
index them so that I can actually use
"+Fname:john +County:USA" as a query?
Erick Erickson wrote:
>
> 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
>
--
View this message in context: http://www.nabble.com/Searching-for-multiple-criteria-%28accross-2-tables%29-tp15502657p15508362.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
Re: Searching for multiple criteria (accross 2 tables)
Posted by Erick Erickson <er...@gmail.com>.
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...@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
>
>