lucene-java-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Emmanuel Bernard <emman...@hibernate.org>
Subject Re: Searching for multiple criteria (accross 2 tables)
Date Sat, 23 Feb 2008 18:51:27 GMT
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_dinh@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


Mime
View raw message