Dominik,
Sorry I saw this a little late, but I asked a similar question back in Dec
2005 and Jeff Rodenburg gave me some splendid help which we are implementing
even now. Here is the gist of his solution.

- Include the Lucene Doc's score (or sequential order, as an int) with the
list of id values.
- Create an XML string, then pass as it as a text parameter to a stored
procedure.  The XML string contains a set of id values and their associated
document score or order value.
- Read the text parameter within a stored procedure into a declared table
using OPENXML
- Join the declared table with your existing resultset code on the id values
- Order the resultset by the order value included in the XML

Here is some sample code for the stored procedure:

/*******************************************************/
create procedure dbo.Get_Records_ByXml (
   @xmlSource    text
)
as

/*
<Documents>
   <Doc>
       <Id>123</Id>
       <ScoreOrder>1</ScoreOrder>
   </Doc>
   <Doc>
       <Id>456</Id>
       <ScoreOrder>2</ScoreOrder>
   </Doc>
   <Doc>
       <Id>789</Id>
       <ScoreOrder>3</ScoreOrder>
   </Doc>
</Documents>
*/

set nocount on
/* xmlHandle */
declare    @xmlHandle            int

-- SQL's internal stored procedure to prep the @xmlSource stream for reading
by OPENXML
exec sp_xml_preparedocument @xmlHandle OUTPUT, @xmlSource

declare    @docs table (
   Id            int,
   ScoreOrder    int
)

-- Use OPENXML to populate declared table
insert into @docs
select    Id,    ScoreOrder
from    openxml (@xmlHandle, '/Documents/Doc',1)
       with    (
           Id                int        'Id',
           ScoreOrder        int        'ScoreOrder'
       )

-- Declared table is populated, ready to be joined with other tables
select    Id, ScoreOrder
from    @docs
order by ScoreOrder asc

-- SQL's internal stored procedure to drop the @xmlSource stream handle from
memory
exec sp_xml_removedocument @xmlHandle

set nocount off
go


declare @text varchar(8000)

select    @text =
'<Documents>' +
' <Doc>' +
'  <Id>123</Id>' +
'  <ScoreOrder>1</ScoreOrder>' +
' </Doc>' +
' <Doc>' +
'  <Id>456</Id>' +
'  <ScoreOrder>2</ScoreOrder>' +
' </Doc>' +
' <Doc>' +
'  <Id>789</Id>' +
'  <ScoreOrder>3</ScoreOrder>' +
' </Doc>' +
'</Documents>'

exec Get_Records_ByXml @[EMAIL PROTECTED]

/*
-- Returns this result

Id    ScoreOrder
----  ----------
123   1
456   2
789   3
*/

Hope that helps!

George


On 6/30/06, Dominik Bruhn <[EMAIL PROTECTED]> 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?


Thanks
--
Dominik Bruhn
mailto: [EMAIL PROTECTED]
http://www.dbruhn.de

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


Reply via email to