Return-Path: 
 <java-user-return-21860-apmail-lucene-java-user-archive=lucene.apache.org@lucene.apache.org>
Delivered-To: apmail-lucene-java-user-archive@www.apache.org
Received: (qmail 59883 invoked from network); 8 Jul 2006 15:06:03 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199)
  by minotaur.apache.org with SMTP; 8 Jul 2006 15:06:03 -0000
Received: (qmail 14164 invoked by uid 500); 8 Jul 2006 15:05:57 -0000
Delivered-To: apmail-lucene-java-user-archive@lucene.apache.org
Received: (qmail 14124 invoked by uid 500); 8 Jul 2006 15:05:57 -0000
Mailing-List: contact java-user-help@lucene.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:java-user-help@lucene.apache.org>
List-Unsubscribe: <mailto:java-user-unsubscribe@lucene.apache.org>
List-Post: <mailto:java-user@lucene.apache.org>
List-Id: <java-user.lucene.apache.org>
Reply-To: java-user@lucene.apache.org
Delivered-To: mailing list java-user@lucene.apache.org
Received: (qmail 14111 invoked by uid 99); 8 Jul 2006 15:05:57 -0000
Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49)
    by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 08 Jul 2006 08:05:57 -0700
X-ASF-Spam-Status: No, hits=2.5 required=10.0
	tests=DNS_FROM_RFC_ABUSE,HTML_MESSAGE,RCVD_IN_BL_SPAMCOP_NET,SPF_PASS
X-Spam-Check-By: apache.org
Received-SPF: pass (asf.osuosl.org: domain of george.abraham.lucene@gmail.com
 designates 64.233.182.186 as permitted sender)
Received: from [64.233.182.186] (HELO nf-out-0910.google.com) (64.233.182.186)
    by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 08 Jul 2006 08:05:55 -0700
Received: by nf-out-0910.google.com with SMTP id h2so348323nfe
        for <java-user@lucene.apache.org>;
 Sat, 08 Jul 2006 08:05:34 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references;
        b=gDBf63Xba1fm5tQpEykhgbsUuURPGpVc5aEopELIpPYM+6GevxBpRH+dWb6bv6ntdQlK+HvVTIZ7JfdLPqHMLQ+AzlWiE+lZynkQa86ZBP1jMfIAfbVJcVlbqMENgPe3JBbSgZZcL0YGC/OCJItYji6pAxFna+RpipFn/SgnhYE=
Received: by 10.49.51.8 with SMTP id d8mr2304512nfk;
        Sat, 08 Jul 2006 08:05:33 -0700 (PDT)
Received: by 10.49.94.18 with HTTP; Sat, 8 Jul 2006 08:05:03 -0700 (PDT)
Message-ID: <9ba0336c0607080805u6a3b2098yc241333bce7feae3@mail.gmail.com>
Date: Sat, 8 Jul 2006 11:05:03 -0400
From: "George Abraham" <george.abraham.lucene@gmail.com>
To: java-user@lucene.apache.org
Subject: Re: Sorting & SQL-Database
In-Reply-To: <200607010110.50154.dominik@dbruhn.de>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_47439_7187360.1152371103357"
References: <200607010110.50154.dominik@dbruhn.de>
X-Virus-Checked: Checked by ClamAV on apache.org
X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N

------=_Part_47439_7187360.1152371103357
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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 @xmlSource=@text

/*
-- Returns this result

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

Hope that helps!

George


On 6/30/06, Dominik Bruhn <dominik@dbruhn.de> 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: dominik@dbruhn.de
> http://www.dbruhn.de
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: java-user-unsubscribe@lucene.apache.org
> For additional commands, e-mail: java-user-help@lucene.apache.org
>
>

------=_Part_47439_7187360.1152371103357--