// 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; }