// SELECT ..., TS_RANK_CD(fulltext, nxquery, 32) as nxscore
// FROM ... LEFT JOIN fulltext ON fulltext.id = hierarchy.id
// , TO_TSQUERY('french', ?) as nxquery
// WHERE ...
// AND NX_TO_TSVECTOR(fulltext) @@ nxquery
// AND fulltext LIKE '% foo bar %' -- when phrase search
// ORDER BY nxscore DESC
@Override
public FulltextMatchInfo getFulltextScoredMatchInfo(
String fulltextQuery,
String indexName,
int nthMatch,
Column mainColumn,
Model model,
Database database) {
String indexSuffix = model.getFulltextIndexSuffix(indexName);
Table ft = database.getTable(model.FULLTEXT_TABLE_NAME);
Column ftMain = ft.getColumn(model.MAIN_KEY);
Column ftColumn = ft.getColumn(model.FULLTEXT_FULLTEXT_KEY + indexSuffix);
String ftColumnName = ftColumn.getFullQuotedName();
String nthSuffix = nthMatch == 1 ? "" : String.valueOf(nthMatch);
String queryAlias = "_nxquery" + nthSuffix;
FulltextMatchInfo info = new FulltextMatchInfo();
info.joins = new ArrayList<Join>();
if (nthMatch == 1) {
// Need only one JOIN involving the fulltext table
info.joins.add(
new Join(
Join.INNER,
ft.getQuotedName(),
null,
null,
ftMain.getFullQuotedName(),
mainColumn.getFullQuotedName()));
}
/*
* for phrase search, fulltextQuery may contain a LIKE part
*/
String like;
if (fulltextQuery.contains(FT_LIKE_SEP)) {
String[] tmp = fulltextQuery.split(FT_LIKE_SEP, 2);
fulltextQuery = tmp[0];
like = tmp[1].replace(FT_LIKE_COL, ftColumnName);
} else {
like = null;
}
info.joins.add(
new Join(
Join.IMPLICIT, //
String.format("TO_TSQUERY('%s', ?)", fulltextAnalyzer),
queryAlias, // alias
fulltextQuery, // param
null,
null));
String tsvector;
if (compatibilityFulltextTable) {
tsvector = ftColumnName;
} else {
tsvector = String.format("NX_TO_TSVECTOR(%s)", ftColumnName);
}
String where = String.format("(%s @@ %s)", queryAlias, tsvector);
if (like != null) {
where += " AND (" + like + ")";
}
info.whereExpr = where;
info.scoreExpr = String.format("TS_RANK_CD(%s, %s, 32)", tsvector, queryAlias);
info.scoreAlias = "_nxscore" + nthSuffix;
info.scoreCol = new Column(mainColumn.getTable(), null, ColumnType.DOUBLE, null);
return info;
}
