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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
