lucene-java-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael D. Curtin" <m...@curtin.com>
Subject Re: Sorting & SQL-Database
Date Sat, 01 Jul 2006 12:36:12 GMT
Dominik Bruhn wrote:
> Hy,
> i use Lucene to index a SQL-Table which contains three fields: a index-field, 
> the text to search in and another field. When adding a lucene document I let 
> Lucene index the search-field and also save the id along with it in the 
> lucene index.
> 
> Uppon searching I collect all ids and add them to a java-string with commas in 
> between to issue a SQL-Query like this one:
> 
> SELECT id,addfield FROM table WHERE id IN ([LUCENERESULT]);
> 
> Where LUCENERESULT is like 2,3,19,3,5.
> 
> This works fine but got one problem: The Search-Result of Lucene is order by 
> relevance and so the id-list is also sorted by relevance. But the result of 
> the SQL-Query is sorted by the id which destroys the relevance-sorting.
> 
> Does anybody know a work-arround?

Sounds like you need to save the score from your Lucene search along with the 
ids, and use them in an ORDER BY clause in your SQL statement.  One way to do 
this would be to put the Lucene results into a working / temporary table, join 
that table into your real table in the query, and ORDER BY score.  Something 
along these lines:

TRUNCATE TABLE tmptable;
INSERT INTO tmptable (id, score) VALUES ([LUCENEID1], [LUCENESCORE1]);
INSERT INTO tmptable (id, score) VALUES ([LUCENEID2], [LUCENESCORE2]);
...

SELECT b.id, b.addfield
FROM tmptable a, table b
WHERE (b.id = a.id)
ORDER BY a.score DESC;

ORDER BY is the normal way in SQL to get ordered results.  In general, there 
is no guarantee of ordering from a SELECT statement without ORDER BY (although 
most RDBMS engines tend to return the rows in the order they were INSERTed, 
possibly perturbed by subsequent DELETEs and UPDATEs).

Good luck!

--MDC

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