Symphony.

Hi,

I get the following SQL Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ELSE 1 END ) AS `score` FROM sym_search_index a' at line 11

An error occurred while attempting to execute the following query

SELECT SQL_CALC_FOUND_ROWS e.id as `entry_id`, data, e.section_id as `section_id`, UNIX_TIMESTAMP(e.creation_date) AS `creation_date`, ( MATCH(index.data) AGAINST ('test') * CASE ELSE 1 END ) AS `score` FROM sym_search_index as `index` JOIN sym_entries as `e` ON (index.entry_id = e.id) WHERE MATCH(index.data) AGAINST ('+test' IN BOOLEAN MODE) AND e.section_id IN ('6','4','5') ORDER BY score desc LIMIT 0, 20

Do you know what might be the problem here? It looks to me that AGAINST ('test') * CASE ELSE 1 END is not the right syntax for the query :-S

Seems that something gets wrong in the weighting.

data.search.php On line 109:

$weighting = '';

If there is no weight, the variable $weighting stays empty, resulting in a faulty SQL statement!

Line 133:

CASE %2$s ELSE 1

Line 151:

$weighting,

So is weighting mandatory?

[update] setting the weight to something other than medium(none) fixes this

Yeah, it looks like if weighting is Medium (none), the weighting-part of the SQL-query gets ignored, resulting in indeed a faulty SQL query.

I changed line 121 of data.search.php from:

if ($weight != 1) $weighting .= sprintf("WHEN e.section_id = %d THEN %d n", $section_id, $weight);

to:

$weighting .= sprintf("WHEN e.section_id = %d THEN %d n", $section_id, $weight);

It solves the issue, but does the whole weighting-aspect still works as expected now?

Good spot. Yep simply removing the if ($weight != 1) will do the trick.

sooooo… why was the if ($weight != 1) there even in the first place? :-P

I added support for use of the multilanguage extension with the search index extension. I’ve sended you a pull request for this.

sooooo… why was the if ($weight != 1) there even in the first place?

So that the CASE are never added if you never choose anything other than Medium — no need to overcomplicate the query if it’s not required. But the reality is that the CASE statement adds such little overhead to the query that it’s fine if it goes in every time.

Thanks for the pull request for language support. I’ve still yet to check out this multilingual stuff, but I must admit I’m not convinced this is the correct approach to take. I see you’ve added a new column to the search table for each language, which isn’t sustainable in my opinion — a new language column storing the language itself would make more sense, with each indexed language as a new row. I’m not keen on adding extension dependencies to the extension, so I’ll look over the multilingual field and see if this can all be addressed in a more generic way.

Cheers!

@nickdunn:

It’s not the multilingual field extension, but the multilanguage support extension I implemented, which aims to convert every field type to a multilingual version.

Guillem has a fork (still version 0.2) of the search index extension for the multilingual field extension. I haven’t looked at the implementation, but/and I second the desire for a generic approach.

Nick, I haven’t been following this extension nearly as much as I should have… How ready for client sites is this? I’m going to be playing around with it in a week or so but before I got too excited I was wondering how far along you are?

Got a few bugs to fix, but we’re using it on client sites and it seems to be holding up nicely.

A quick question.

I have to create new data sources for my sections to apply the $ds-search if those data sources are already filtered by URL keywords or using required url parameters, right?

edit: Nevermind the question above I just found my new years resolution. RTFM and if still in doubt read it again and test it, before asking stupid questions. :-)

Search Index updated to version 0.5 on 5th of December 2010

I have to create new data sources for my sections to apply the $ds-search if those data sources are already filtered by URL keywords or using required url parameters, right?

Most likely, yep. Hope you got it working!

Search Index updated to version 0.6.1 on 5th of December 2010

The last two updates have added some cool stuff. The biggest addition is the concept of “synonyms”. This allows you to build a list of word replacements for misspellings or variations of phrases. So if you see a lot of people searching for “ds” or “datasource” when in reality your content uses the phrase “data source”, then add ds and datasource as synonyms of the word data source and these replacements will be made on user’s searches.

I’ve also neated up the UI by adding more useful columns to the Indexes table, enabled the extension to have its own navigation group in the backend (to allow for more options in the future), and added more contextual help text.

Search Index updated to version 0.6.4 on 5th of December 2010

This release adds coolness in the form of a page to view and interrogate the search logs. If you use Google Analytics you should always set up search tracking as it’s superior to this, but this is a great to quickly see:

• what people are searching for, should they be modified with synonyms
• which phrases yield no or few results
• which phrases yield (too) many results
• how many pages of results users click through

Nick, you are tireless! This is one of the coolest extensions I’ve ever seen. :)

The amount of high quality extensions you pump out is staggering. You must have found a way to duplicate the hours per day or have some secret group of code guru’s at hand. Thanks so much Nick.

Personaly, I think Nick is really a robot and never sleeps. Most of my favourite extensions are his.

Is anyone else have issues with duplicate entries? See attached screenshot.

I have only indexed one section, with two fields. Both textbox fields.

The strange this is, when I index the section then refresh the page - the number of indexed entries doubles, or triples. Is that supposed to happen?

This is screwing up my pagination as the search data-source is saying there are 2 pages but the data-source that I am filtering with $ds-search is only showing one of each duplicated entry, which is good! The search ds just shouldn’t be showing more than one entry if it’s a duplicate imo :s

I’m using v2.1.2

Ideas??

Attachments:

Screen shot 2010-12-09 at 10.01.12 AM.png