You are viewing a plain text version of this content. The canonical link for it is here.
Posted to java-user@lucene.apache.org by Dominik Bruhn <do...@dbruhn.de> on 2006/07/01 01:10:50 UTC
Sorting & SQL-Database
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
Re: Sorting & SQL-Database
Posted by Lukas Vlcek <lu...@gmail.com>.
Hi,
Looking at your problem I can think of one solution for small and
*midsize* result sets. (And I have to say it may be similar to what
Aleksander proposes).
Write workaround query in the following form:
select addfield from (
select addfield, generated_counter from table where id = 2
union
select addfield, generated_counter from table where id = 4
union
select addfield, generated_counter from table where id = 1
...
select addfield, generated_counter from table where id = 3
) order by generated_counter;
I am not sure but that generated counter could be taken from DB
sequence (something like select seq.nextvalue() from dual) or
generated in Java while you compose the SQL statement.
Well, this is what I have on my mind while I've just got up and my
brain is still booting...
Lukas
On 7/3/06, Monsur Hossain <mo...@gmail.com> wrote:
> On 6/30/06, Dominik Bruhn <do...@dbruhn.de> wrote:
> > 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?
>
> We have the same issue and solve this through code. As you're
> generating the list of IDs for the SQL query, also generate a hash
> that maps the ID to its position. When reading back the data from the
> database, read the position hash, and insert the item into the
> appropriate position in an array. Yeah, you're doing the sorting
> yourself, but in our case, we're only returning 10 items per page, so
> its not a huge performance hit.
>
> ---------------------------------------------------------------------
> 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
Re: Sorting & SQL-Database
Posted by Monsur Hossain <mo...@gmail.com>.
On 6/30/06, Dominik Bruhn <do...@dbruhn.de> wrote:
> 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?
We have the same issue and solve this through code. As you're
generating the list of IDs for the SQL query, also generate a hash
that maps the ID to its position. When reading back the data from the
database, read the position hash, and insert the item into the
appropriate position in an array. Yeah, you're doing the sorting
yourself, but in our case, we're only returning 10 items per page, so
its not a huge performance hit.
---------------------------------------------------------------------
To unsubscribe, e-mail: java-user-unsubscribe@lucene.apache.org
For additional commands, e-mail: java-user-help@lucene.apache.org
Re: Sorting & SQL-Database
Posted by George Abraham <ge...@gmail.com>.
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 <do...@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
>
>
Re: Sorting & SQL-Database
Posted by "Aleksander M. Stensby" <al...@integrasco.no>.
Well, it is common in most databasesystems, that if you dont specify a
sort, you get the results sorted by id, or by when the rows are inserted
into the db.
The quickest way for you is to write around your query.
instead of doing one query, just do where queries with equals. this would
produce a bit overflow if you have your database on an external server,
but again, the quickest query to ever run in sql are equals-queries. Since
the ID is unique (i hope so), it should be no problem at all.
... WHERE id = 2;
... WHERE id = 3
... WHERE id = 19
and so on would give you the correct relevance-order.
On Sat, 01 Jul 2006 10:09:01 +0200, karl wettin <ka...@snigel.net> wrote:
> On Sat, 2006-07-01 at 01:10 +0200, Dominik Bruhn wrote:
>
>> 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?
>
> This is really a question you should ask in the forum of your RDBMS. You
> could always execute multiple SQL-queries within the same statement
> without too much loss. But I'm certain there is a way to enforce the
> order as you specified it in the WHERE-clause.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: java-user-unsubscribe@lucene.apache.org
> For additional commands, e-mail: java-user-help@lucene.apache.org
>
>
--
Aleksander M. Stensby
Software Developer
Integrasco A/S
aleksander.stensby@integrasco.no
Tlf.: +47 41 22 82 72
---------------------------------------------------------------------
To unsubscribe, e-mail: java-user-unsubscribe@lucene.apache.org
For additional commands, e-mail: java-user-help@lucene.apache.org
Re: Sorting & SQL-Database
Posted by karl wettin <ka...@snigel.net>.
On Sat, 2006-07-01 at 01:10 +0200, Dominik Bruhn wrote:
> 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?
This is really a question you should ask in the forum of your RDBMS. You
could always execute multiple SQL-queries within the same statement
without too much loss. But I'm certain there is a way to enforce the
order as you specified it in the WHERE-clause.
---------------------------------------------------------------------
To unsubscribe, e-mail: java-user-unsubscribe@lucene.apache.org
For additional commands, e-mail: java-user-help@lucene.apache.org
Re: Sorting & SQL-Database
Posted by "Michael D. Curtin" <mi...@curtin.com>.
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