AjaxSearch 'relevance' mode throws database error

1 Posts

Zombie Reply #1, 16 years, 8 months ago

Whenever I set AjaxSearch snippet to ’relevance’ mode, the ajax search spews the following error upon search:

Cannot query the database (SELECT DISTINCT sc.id, sc.pagetitle, sc.description, sc.content, sc.introtext FROM `modx_site_content` LEFT JOIN `modx_site_tmplvar_contentvalues` stc ON sc.id = stc.contentid sc WHERE MATCH (sc.pagetitle, sc.longtitle, sc.introtext, sc.description, sc.content, stc.value) AGAINST ('Utopia') AND sc.privateweb = 0 AND sc.published = 1 AND sc.searchable=1 AND sc.deleted=0;)

The only topic I can seem to find on the forums that is related is from more than a year ago.

When I run this query from the terminal, MySQL says:

ERROR 1064 (42000): 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 'sc WHERE MATCH (sc.pagetitle, sc.longtitle, sc.introtext, sc.description, sc.con' at line 1

I’m running Ubuntu Dapper with MySQL 5.0.22 and PHP 4.4.2-1

27 Posts

wiseguy Reply #2, 16 years, 7 months ago

I am having exactly the same problem Deadguy, gonna look into it today and try to fathom out the problem. It’s definitely just a problem with the SQL.

MODX Staff
10,725 Posts

opengeek Reply #3, 16 years, 7 months ago

Two problems...

1. The table alias `sc` is supposed to apply to `modx_site_content` but it seems the LEFT JOIN on `modx_site_tmplvar_contentvalues` has been inserted in between the table name and the sc alias. This is a SQL syntax error.

2. There are several fields in the MATCH arguments that are not part of the full-text index declared on modx_site_content. All field arguments in MATCH must be in the declared full-text index. That means you can only use this search against pagetitle, description and content from the modx_site_content table. None of the other arguments are valid and would require significant db structure changes to accomplish.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

1 Posts

Zombie Reply #4, 16 years, 7 months ago

Well, now that we know what’s seemingly wrong, any idea how to fix it? Or is this a problem with the AjaxSearch plugin?

27 Posts

wiseguy Reply #5, 16 years, 7 months ago

I also found the same answers, so I set the two extra fields up as Indexes in the database. The script worked, but actually the results were much worse.

Final outcome? I reverted back to ’partial’ mode and slightly amended my ’partial’ SQL statement to something more specific for this job.

If however you need a quick way to get the script to work in ’relevance’ mode, then you simply open up assets/snippets/AjaxSearch/includes/AjaxSearch.inc.php and look for the following line:

$sql .= "FROM $tbl_sc" . $tbl_sql . " sc WHERE ";

Replace with:

$sql .= "FROM $tbl_sc sc" . $tbl_sql . "  WHERE ";

(This will move the table alias to the correct place).

And then find the line:

$sql .= "MATCH (sc.pagetitle, sc.longtitle, sc.introtext, sc.description, sc.content, stc.value) AGAINST ('{$searchTerm}') AND ";

And replace it with:

$sql .= "MATCH (sc.pagetitle, sc.description, sc.content) AGAINST ('{$searchTerm}') AND ";

Just to explain: in the database (within the ’site_content’ table) the fields ’longtitle’ and ’introtext’ are not indexed using FULLTEXT so cannot be searched using the SQL function MATCH(). This is my understanding anyway!!!

Hope that helps.