javascript - 根据搜索词的相关性对多个 JavaScript 数组进行合并和排序

Question

我认为something similar之前已经有人问过这个问题，但我似乎无法弄清楚。我的 cordova 应用程序中有两个来自 SQL 数据库的数组。是否可以组合这两个数组并按与搜索查询的最佳匹配进行排序。

在我的应用程序中，我有一个搜索功能，可以按最佳匹配搜索多个本地表和订单。这很好用

SQL 查询示例:

tx.executeSql("SELECT * FROM contacts WHERE f_name LIKE ? ORDER BY (CASE WHEN f_name = ? THEN 1 WHEN f_name LIKE ? THEN 2 ELSE 3 END), f_name ",["%" + query + "%", query, query + "%"],onSuccess, onError);

重复此过程，并为正在搜索的不同表创建不同的数组。

我剩下两张表，一张用于用户，一张用于消息内容。 SQL 查询运行回调函数后，将所有结果排序到 1 个数组中:

var allResults = [];
// create an array of all

for(var x = 0; x < allChatSearchResults.users.length; x++){
    var result = allChatSearchResults.users[x];
    result['type'] = 'user';
    allResults[allResults.length] = result;
}
for(var x = 0; x < allChatSearchResults.messages.length; x++){
    var result = allChatSearchResults.messages[x];
    result['type'] = 'message';
    allResults[allResults.length] = result;
}

有没有一种方法可以用来循环遍历数组 allResults 并重新排序该数组以匹配搜索查询。

搜索“user”时的数组输出如下:

allResults = [
{
    type : 'user',
    f_name : 'username',
    l_name : 'somename'
},
{
    type : 'message',
    message : 'this is my user name', //////   query from sql matches this
    date : '11/04/2016'
},
{
    type : 'message',
    message : 'another containing username', //////   query from sql matches this
    date : '09/04/2016'
},
//and so on
];

完整代码:

var searchResultsSource = 0;
var allChatSearchResults = {users : [], messages : []};
var searchChatResults = function (type, query, res){
    searchResultsSource++;
    if(res.length > 0){
        for(var x = 0; x < res.length; x++){ allChatSearchResults[type][x] = res[x]; }
    }
    if(searchResultsSource == 2){
        //run the function
        console.log('Search Results', allChatSearchResults);
        var allResults = [];
        //  create an array of all
        for(var x = 0; x < allChatSearchResults.users.length; x++){
            var result = allChatSearchResults.users[x];
            result['type'] = 'user';
            allResults[allResults.length] = result;
        }
        for(var x = 0; x < allChatSearchResults.messages.length; x++){
            var result = allChatSearchResults.messages[x];
            result['type'] = 'message';
            allResults[allResults.length] = result;
        }
        //  create an array of just search query

        //  reorder the array based on the ordered search query


    }
};
var searchChats = function () {
    var query = $('#activeChatSearch').val();
    query = query.toLowerCase();
    if(query.length > 3){
        //search my chats
        //search users and search messages
        db.transaction(function(tx) {
            var users = [];
            var chats = [];
            var messages = [];
            tx.executeSql("SELECT * FROM contacts WHERE f_name LIKE ? ORDER BY (CASE WHEN f_name = ? THEN 1 WHEN f_name LIKE ? THEN 2 ELSE 3 END), f_name ",["%" + query + "%", query, query + "%"], function (tx, res){
                if(res.rows.length == 0){ console.log('no users with first name ' + query); }
                for(var x = 0; x < res.rows.length; x++){ users[x] = res.rows.item(x); }
                searchChatResults('users', query, users);
            }, function (tx, error){
                console.log(error.message);
            });
            tx.executeSql("SELECT * FROM active_chats ORDER BY last_changed DESC", [], function (tx, res){
                if(res.rows.length > 0){
                    var count = 0;
                    var max = res.rows.length;
                    var results = res.rows;
                    var searchThisChat = function (tx, query, chatID){
                        tx.executeSql("SELECT * FROM chat_" + chatID + " WHERE message LIKE ? ORDER BY (CASE WHEN message = ? THEN 1 WHEN message LIKE ? THEN 2 ELSE 3 END), message LIMIT 0,1", ["%" + query + "%", query, query + "%"], function (tx, res){
                            if(res.rows.length == 0){ /*console.log('No chats match query',messages); */ }
                            else { messages[messages.length] = res.rows.item(0); }
                            if(count == max){ searchChatResults('messages', query, messages); } else{ searchThisChat(tx, query, results.item(count).chat_id); count++; }
                        }, function (tx, e){
                            console.log(e.message);
                            if(count == max){ searchChatResults('messages', query, messages); } else{ searchThisChat(tx, query, results.item(count).chat_id); count++; }
                        });
                    };
                    searchThisChat(tx, query, res.rows.item(0).chat_id);
                }
            }, function (tx, e){
                console.log(e.message);
            });
        }, function(err) {
            console.log('Open database ERROR: ', err);
        });

        var activeChats = $('#activeChats');
        activeChats.html('');
        for(var x = 0; x < 6; x++){
            var item = formatCurrentChatItem(
                {f_name : query, l_name : 'Bloggs', 'user_id' : 18},
                null,
                null
            );
            item.appendTo(activeChats);
        }
    }
};
$(function () {
    $('#activeChatSearch').on('input', searchChats);
    $('#activeChatSearchBtn').on('click', searchChats);  <-   input function
});

Answer 1

由于您没有在返回的 SQL 中包含相关性分数，因此无法组合两个数组。

首先，您需要将分数作为 SQL 的一部分返回:

tx.executeSql("SELECT *,(CASE WHEN f_name = ? THEN 1 WHEN f_name LIKE ? THEN 2 ELSE 3 END) as Score FROM Contacts WHERE f_name LIKE ? ORDER BY (CASE WHEN f_name = ? THEN 1 WHEN f_name LIKE ? THEN 2 ELSE 3 END), f_name ",["%"+ query + "%", query, query + "%"],onSuccess, onError);

然后您将能够组合数组并按“分数”列排序:

allResults.sort(function(a,b){ return a.Score-b.Score; });