所以我的sphinx.conf文件包含类似的东西。 基本上我使用delta索引来快速完成任务。
source main { #... sql_query_pre = SET NAMES utf8 sql_query_pre = REPLACE INTO sph_counter SELECT 1, MAX(id) FROM photo sql_query = \ SELECT p.id AS id, p.search AS search, COUNT(li.id) AS total_likes \ FROM `photo` p \ LEFT JOIN `like` li \ ON p.id = li.photo_id \ WHERE p.id <= ( SELECT max_doc_id FROM sph_counter WHERE counter_id=1 ) \ GROUP BY \ p.id #... sql_query_info = SELECT * FROM photo WHERE id=$id } source delta : main { sql_query_pre = SET NAMES utf8 sql_query = \ SELECT p.id AS id, p.search AS search, COUNT(li.id) AS total_likes \ FROM `photo` p \ LEFT JOIN `like` li \ ON p.id = li.photo_id \ WHERE p.id > ( SELECT max_doc_id FROM sph_counter WHERE counter_id=1 ) \ GROUP BY \ p.id }而在我检索数据时,我也希望有一些排序方法。
$s->SetSortMode(SPH_SORT_EXTENDED, '@relevance DESC, total_likes DESC, @id DESC'); $result = $s->Query($data['query'], "delta main");当我只有main索引时,排序工作正常。 但现在当我使用两个索引进行搜索时,delta索引的结果会附加在前面。 我真正想要的是两个索引的结果被提取然后根据偏好排序,即@relevance DESC, total_likes DESC, @id DESC在我的情况下。 这就是total_likes应优先于id
So my sphinx.conf file contains something similar. Basically I am using delta index to make things quick.
source main { #... sql_query_pre = SET NAMES utf8 sql_query_pre = REPLACE INTO sph_counter SELECT 1, MAX(id) FROM photo sql_query = \ SELECT p.id AS id, p.search AS search, COUNT(li.id) AS total_likes \ FROM `photo` p \ LEFT JOIN `like` li \ ON p.id = li.photo_id \ WHERE p.id <= ( SELECT max_doc_id FROM sph_counter WHERE counter_id=1 ) \ GROUP BY \ p.id #... sql_query_info = SELECT * FROM photo WHERE id=$id } source delta : main { sql_query_pre = SET NAMES utf8 sql_query = \ SELECT p.id AS id, p.search AS search, COUNT(li.id) AS total_likes \ FROM `photo` p \ LEFT JOIN `like` li \ ON p.id = li.photo_id \ WHERE p.id > ( SELECT max_doc_id FROM sph_counter WHERE counter_id=1 ) \ GROUP BY \ p.id }And in the php when I retrieve data I also want to have some sort of sorting methods.
$s->SetSortMode(SPH_SORT_EXTENDED, '@relevance DESC, total_likes DESC, @id DESC'); $result = $s->Query($data['query'], "delta main");Sorting was working fine when I had only main index. But now when I search with both indexes, results from the delta index is appended at the front. What I actually want is results from both indexes are fetched and then sorted according to preferences i.e. @relevance DESC, total_likes DESC, @id DESC in my case. That is total_likes should be given preference over id
最满意答案
感谢@barryhunter的解决方案 。 解决方案是在delta索引中第二个sql_query_pre必须被覆盖。
sql_query_pre = SET NAMES utf8 sql_query_pre = sql_query = \Thanks @barryhunter for the solution. The solution was that in the delta index second sql_query_pre had to be overwritten.
sql_query_pre = SET NAMES utf8 sql_query_pre = sql_query = \更多推荐
发布评论