No search results found (but searchstring is in database)

[ads]

I have a blogposting which i want to cite from time to time. I recognized that this posting isn't found by using the regular blog search, instead i have to use Google

Digging deeper into the problem i found out that the problem must be somewhere in or around serendipity_searchEntries(). During the first call of this function a query is build and executed which finds the specific searchstring. Exactly one result is returned. This seems not to be enough, but instead the function is called a second time, including a '*' at the end of the search.

I don't know why the '*' is added at all, because now the database searches for the exact string, including the '*' - obviously no result is returned and i get a "No entries to print".

(Just in case: the database is PostgreSQL )

[onli]

Didn't you yourself suggested a patch fixing this for postgresql?

The * is added because those search finds additional entries in which the searchterm is part of a word, not the word alone. This didn't work for postgresql as far as i remember, don't know why - but there was a bugreport.

[ads]

Do you mean the GROUP BY issue? Just checked my posting list here in the forum and can't see a posting of mine about this issue.

I can however explain why the '*' isn't working in PostgreSQL.
Either the fulltext search engine is used - in this case it makes no sense to add the '*', because a '*' is never a part of a word and is removed by tsearch anyway.
If just a plain LIKE/ILIKE is used for the search, then PostgreSQL assumes you are searching for the plain '*':

Code: Select all

ads=# SELECT true WHERE '*' ILIKE '*';
 bool
------
 t
(1 row)

The '*' will not search for "any further text", LIKE knows '%' for this case (which is used anyway in the query).

[onli]

Ok - then it's likely i mixed it up with http://board.s9y.org/viewtopic.php?p=94317#p94317 . The patch could work accordingly.

[Cenic]

Hi,

I'm not sure if this is related but currently I'm also looking into a problem where the search does not find matching entries and I'm also running PostgreSQL (8.4.2). In my case I narrowed it down to the case where more than one word is entered into the search form. Are you using multiple words in the search form?

It seams that with tsearch a search for 'foo bar' will basically lead to the following function evaluation:

Code: Select all

s9y@prd=> select to_tsquery('foo bar');
ERROR:  syntax error in tsquery: "foo bar"

The full text search expects the argument to to_tsquery to be words and operators and not a string with spaces in it. I came up with the following patch that should improve full text search:

Code: Select all

*** functions_entries.inc.php.orig      Wed Nov 25 21:41:43 2009
--- functions_entries.inc.php   Sun May  2 16:09:57 2010
***************
*** 761,771 ****
                                      WHERE routine_name LIKE 'to_tsvector'
                                        AND specific_catalog = '" . $serendipity['dbName'] . "'");
          if (is_array($r) && $r[0]['counter'] > 0) {
!             $term = str_replace('&', '&', $term);
              $cond['find_part'] = "(
!             to_tsvector('english', title)    @@to_tsquery('$term') OR
!             to_tsvector('english', body)     @@to_tsquery('$term') OR
!             to_tsvector('english', extended) @@to_tsquery('$term')
              )"; 
          } else {
              $cond['find_part'] = "(title ILIKE '%$term%' OR body ILIKE '%$term%' OR extended ILIKE '%$term%')";
--- 761,772 ----
                                      WHERE routine_name LIKE 'to_tsvector'
                                        AND specific_catalog = '" . $serendipity['dbName'] . "'");
          if (is_array($r) && $r[0]['counter'] > 0) {
!             $term = html_entity_decode($term, ENT_QUOTES);
!             $term = trim(strtr($term, "'\"", "  "));
!             $term = preg_replace('/ +/', "|", $term);
              $cond['find_part'] = "(
!             to_tsvector('english', coalesce(title,'')||coalesce(body,'')||coalesce(extended,''))
!             @@ to_tsquery('$term')
              )";
          } else {
              $cond['find_part'] = "(title ILIKE '%$term%' OR body ILIKE '%$term%' OR extended ILIKE '%$term%')";

A careful review is advised

Regards,
Stefan

[ads]

onli: Looks like this is the same issue. Maybe the patch should not only be applied to SQLite but also to the PostgreSQL part (or probably to any !MySQL, because '%' is SQL standard).

Cenic: The patch looks OK. Does it work for you? (Haven't tested the patch on my blog.)

[Cenic]

Cenic: The patch looks OK. Does it work for you? (Haven't tested the patch on my blog.)

I worked on this problem only yesterday and wanted to discuss it here when I realized that there was already a posting. I have the patch implemented on my blog since yesterday and it seems to work. On the other hand I don't have many visitors using the search function and therefore this might not be significant. At least in all my tests I get much better search results than before.

I'm not sure about special cases like the user searching for quotation marks and other special characters. We don't want any injections, right...

Regards,
Stefan