The score alias retrieved in the Views query via the Similar entries views argument when including CCK fields (an argument option) always has a value of wither 1 or 0. This is not an accurate score as FULLTEXT scores are large decimals. The view ends up sorting by node ID, or basically the order in which the nodes appear in the database.

The difficulty in including CCK fields in a FULLTEXT search is that the query needs to select multiple tables while each table has its own index. Similar entries 2.x was developed so CCK tables are reindexed on cron runs via hook_cron(). When cron is run Similar entries checks to ensure that the current index on each CCK field table matches the current setup of fields for the node type. If the index does not match then the table is reindexed to include all the field columns in a single index. This is because in the query we can only query one index at a time, so we would otherwise be querying several times on the same tables, and it is more accurate to group all the fields of a content type in the same index. The current query runs successfully but is very inaccurate, thus this is a significant bug that must be fixed before a public release.

If anyone could look at the current query that runs and tell why the score alias is only receiving 1s and 0s when querying node titles, bodies, and CCK fields, and write a version of the query that works properly then I could certainly reorganize the similar entries argument to perform the proper query. The query runs great when on the single node table, just not when selecting multiple tables. Ideally, a query needs to be able to get an accurate score that includes the comparisons against all the fields in all the tables.

Comments

jordojuice’s picture

jordojuice commented

This is an example of the query that is run currently in Views. This is in the 6.x version and the query is running on node title, body, and a few other fields in the page node type. It does produce results, but not in any proper order.

SELECT node.nid AS nid,
   MATCH (node_revisions.body, node_revisions.title) AGAINST ('HOT DOG BUNS.') OR MATCH (content_type_page.field_test_value, content_type_page.field_another_test_value, content_type_page.field_and_one_more_value) AGAINST ('HOT DOG BUNS.') AS score,
   node.title AS node_title,
   COUNT(node.nid) AS node_count
 FROM node node 
 LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
 LEFT JOIN content_type_page content_type_page ON node.vid = content_type_page.vid
 WHERE (node.moderate = 0) AND (node.status <> 0) AND (node_revisions.nid <> 4)
 GROUP BY nid, score, node_title
 HAVING score > 0
 ORDER BY score DESC

I've seen that this type of binary result can occur when all fields are not indexed, but the indexing is working properly as verified in phpMyAdmin. There must be some reason that it is switching to boolean mode though.

jordojuice’s picture

jordojuice commented
Title: Similar entries argument scoring fails when including CCK fields » Argument query goes into boolean mode when including CCK fields
jordojuice’s picture

jordojuice commented
Status: Active » Closed (won't fix)

This issue is irrelevant. Similar Entries now uses BOOLEAN MODE queries intentionally, as it allows us to increase the relevance of words within the node title.

jordojuice’s picture

jordojuice commented
Issue summary: View changes

Added a note that this only happens when including CCK fields.