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