lucene-java-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Streeton" <mike.stree...@ardentia.co.uk>
Subject RE: Sorting & SQL-Database
Date Tue, 04 Jul 2006 08:15:48 GMT
The simplest solution to this I would suggest is to decode the id to
relevance score e.g.

Select id, addfield
>From mytable
Where id in (1,2,3,4,5,50,60,70)
Order by case id when 1 then 0.9 when 2 then 0.8 when 3 then 0.7 ....
end desc

You will have to generate the in () and the case statement but this will
do it. It will still go pear shaped when two ids have the same score
unless you fiddle this.

Mike


www.ardentia.com the home of NetSearch
-----Original Message-----
From: Lukas Vlcek [mailto:lukas.vlcek@gmail.com] 
Sent: 04 July 2006 05:54
To: java-user@lucene.apache.org
Subject: Re: Sorting & SQL-Database

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 <monsurh@gmail.com> wrote:
> On 6/30/06, Dominik Bruhn <dominik@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


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