时间戳

首页 > 解决方案 > 多个表之间的复杂搜索在 Parse Server 中不起作用？

parse-platform - 多个表之间的复杂搜索在 Parse Server 中不起作用？

问题描述

我有 4 张桌子：

  1. ForumPost
  2. ForumReply
  3. ForumComment
  4. SearchTerms

表之间的关系：

请查看此图片

SearchTerms 表字段：

  1. objectId
  2. ACL
  3. SearchString：此列包含字符串或相关对象（ForumPost、ForumComment、ForumReply）我们将使用此列搜索任何对象或相关表
  4. objectTypeName：这包含参考表的名称（ForumPost、ForumComment、ForumReply）
  5. connectedObjectId：这包含来自表 ForumPost、ForumComment、ForumReply 的相关对象的 objectId

ForumPost 表字段：

  1. objectId
  2. replies:(Array) 此字段包含ForumReply数组中的所有对象（附示例）
  3. ACL
  4. title
  5. description
//回复栏示例

    [
      {
        "__type": "指针",
        "className": "论坛回复",
        “objectId”：“LSVO4KwHxO”
      },
      {
        "__type": "指针",
        "className": "论坛回复",
        “objectId”：“EQA9Fotvp5”
      },
      {
        "__type": "指针",
        "className": "论坛回复",
        “objectId”：“smpfWT8fbq”
      }
    ]

论坛回复表字段：

  1. objectId
  2. text: 回复说明
  3. to: 这是ForumPost表的指针
  4. searchTerm: 这是searchTerms表的指针

  5. comments：（数组）该字段包含ForumComment数组中的所有对象（附示例） 

    //评论字段示例


    [
  {
    "__type": "Pointer",
    "className": "ForumComment",
    "objectId": "FQwqHdVX7I"
  }

    

]

论坛评论表字段：

  1. objectId
  2. text: 回复说明
  3. to: 这是ForumReply表的指针
  4. searchTerm: 这是searchTerms表的指针

我的目标是什么：我有一个网络表单，用户可以在其中输入搜索字符串，我想获取所有ForumPost拥有该字符串的人。搜索也将ForumnReply适用ForumComments。所以回复和评论有字符串的帖子也会在结果中。

我做了什么：：首先我在SearchTerms表中运行搜索（因为这个表有所有表ForumPost，ForumReply，ForumComment的搜索字符串，所以现在我有所有匹配对象的ID（ForumPost，ForumComment，ForumReply）。在此之后我尝试使用OR带有结果 id 的查询（使用containedIn）并且也尝试使用Aggregates但没有返回所有匹配的帖子。

请建议是否有更好的方法来运行这个复杂的搜索。

这是我的代码：

Parse.Cloud.define("searchForumPosts", function(request, response) {
    isRequestLegitimate(request).then(function(result) {
        if (result.legitimateRequest) {
            var query = new Parse.Query("ForumPost");
            var completeLength = 0;
            findTextInSearchTerms(query, request.params.wildcard, "searchTerms").then(function(ids) {
                var query2 = new Parse.Query("ForumPost");
                if ((ids == -1 || ids.length == 0)) {
                    completeLength = 0;
                    return [];
                } else {
                    completeLength = ids.length;
                    // not very efficient, if the wildcard is empty we still ask for findTextInSearchTerms, change that later on
                    query2.containedIn("objectId", ids);

                    if (request.params.pageSize && request.params.pageNumber) {
                        var pageSize = parseInt(request.params.pageSize);
                        var pageNumber = parseInt(request.params.pageNumber);

                        query2.limit(pageSize);
                        if (pageNumber > 1) {
                            query2.skip((pageNumber - 1) * pageSize);
                        }
                    }
                    // query2.include("offer");
                    // query2.include("offer.artist");
                    query2.include("creator");
                    query2.descending("createdAt");
                    query2.select("objectId", "offer","postDeleted",  "title", "text", "creator", "creator.firstname", "creator.lastname", "replies");
                    return query2.find({
                        useMasterKey: true
                    });
                }
            }, function(error) {
                return error;
            }).then(function(foundPosts) {
                console.log('foundPosts',foundPosts);
                if (foundPosts.length > 1) {
                    var sortBy = request.params.sortBy;
                    if (sortBy == "artist") {
                        foundPosts.sort(function(a, b) {
                         console.log('foundPosts a',a);
                         console.log('foundPosts b',b);


                            var nameA = 'ZZZZZZZZZ';
                            var nameB = 'ZZZZZZZZZ';
                            if (a.offer) {
                                nameA = ((a.offer || {}).artist || {}).lastname.toUpperCase();
                            }
                            if (b.offer) {
                                nameB = ((b.offer || {}).artist || {}).lastname.toUpperCase();
                            }

                            if (nameA < nameB) {
                                return -1;
                            }
                            if (nameA > nameB) {
                                return 1;
                            }
                            // names must be equal
                            return 0;
                        });
                    } else if (sortBy == "author") {
                        foundPosts.sort(function(a, b) {
                            var nameA = 'ZZZZZZZZZ';
                            var nameB = 'ZZZZZZZZZ';
                            if (a.offer) {
                                nameA = ((a.offer || {}).creator || {}).lastname.toUpperCase();
                            }
                            if (b.offer) {
                                nameB = ((b.offer || {}).creator || {}).lastname.toUpperCase();
                            }

                            if (nameA < nameB) {
                                return -1;
                            }
                            if (nameA > nameB) {
                                return 1;
                            }
                            // names must be equal
                            return 0;
                        });
                    }
                }
                console.log('foundPostsfoundPosts',foundPosts);

                var results = {};
                results.completeLength = completeLength;
                results.posts = foundPosts;
                response.success(results);
            }, function(error) {
                response.error(error);
            });
        } else {
            response.error("You must be logged in!");
        }
    });
});
function findTextInSearchTerms(motherQuery, wildcard, pattern, objectType) {
    console.log('#findTextInSearchTerms Woldcard',wildcard);
    console.log('#findTextInSearchTerms motherQuery',motherQuery);
    console.log('#findTextInSearchTerms pattern',pattern);
    console.log('#findTextInSearchTerms objectType',objectType);

    var orQuery = null;
    var promise = new Parse.Promise();
    var searchTermArray = null;
    var isArray = false;
    var searchNeeded = true;
    var filteredWildcard = []



    console.log('#findTextInSearchTerms Woldcard',wildcard);
    console.log('#findTextInSearchTerms motherQuery',motherQuery);

    if (Array.isArray(wildcard)) {
        isArray = true;
        searchTermArray = wildcard
    } else {
        var lowerCase = (wildcard || '').toLowerCase();
        lowerCase = lowerCase.trim();
        // wildcard.replace(/[^a-zA-Z0-9]/g, "")
        lowerCase = lowerCase.replace(/[^\w\s]/gi, '');
        searchTermArray = lowerCase.split(" ");
    }
    if (wildcard.length < 2 && isArray == false) {
        searchNeeded = false;
    }

    for (const element of searchTermArray) {
      if((element.trim()).length > 1){
        filteredWildcard.push(element);
      }
    }
    console.log('filteredWildcard',filteredWildcard);
    wildcard = filteredWildcard;

    if (searchNeeded) {
        console.log('#findTextInSearchTerms inside searchNeeded',wildcard);
        console.log('#findTextInSearchTerms inside searchTermArray',searchTermArray);

        //motherQuery.matches(pattern, regex);
        if (searchTermArray.length == 1) {
            console.log('#findTextInSearchTerms length == 1');
            var query1 = new Parse.Query("SearchTerms");
            query1.contains("searchString", searchTermArray[0]);
            orQuery = Parse.Query.or(query1);
        } else if (searchTermArray.length == 2) {
            console.log('#findTextInSearchTerms length == 2');

            var query1 = new Parse.Query("SearchTerms");
            query1.contains("searchString", searchTermArray[0]);
            var query2 = new Parse.Query("SearchTerms");
            query2.contains("searchString", searchTermArray[1]);
            orQuery = Parse.Query.or(query1, query2);
        } else if (searchTermArray.length == 3) {
            console.log('#findTextInSearchTerms length == 3');

            var query1 = new Parse.Query("SearchTerms");
            query1.contains("searchString", searchTermArray[0]);
            var query2 = new Parse.Query("SearchTerms");
            query2.contains("searchString", searchTermArray[1]);
            var query3 = new Parse.Query("SearchTerms");
            query3.contains("searchString", searchTermArray[2]);
            orQuery = Parse.Query.or(query1, query2, query3);
        } else if (searchTermArray.length == 4) {
            console.log('#findTextInSearchTerms length == 4');

            var query1 = new Parse.Query("SearchTerms");
            query1.contains("searchString", searchTermArray[0]);
            var query2 = new Parse.Query("SearchTerms");
            query2.contains("searchString", searchTermArray[1]);
            var query3 = new Parse.Query("SearchTerms");
            query3.contains("searchString", searchTermArray[2]);
            var query4 = new Parse.Query("SearchTerms");
            query4.contains("searchString", searchTermArray[3]);
            orQuery = Parse.Query.or(query1, query2, query3, query4);
        } else if (searchTermArray.length == 5) {
            console.log('#findTextInSearchTerms length == 5');

            var query1 = new Parse.Query("SearchTerms");
            query1.contains("searchString", searchTermArray[0]);
            var query2 = new Parse.Query("SearchTerms");
            query2.contains("searchString", searchTermArray[1]);
            var query3 = new Parse.Query("SearchTerms");
            query3.contains("searchString", searchTermArray[2]);
            var query4 = new Parse.Query("SearchTerms");
            query4.contains("searchString", searchTermArray[3]);
            var query5 = new Parse.Query("SearchTerms");
            query5.contains("searchString", searchTermArray[4]);
            orQuery = Parse.Query.or(query1, query2, query3, query4, query5);
        } else if (searchTermArray.length == 6) {
            console.log('#findTextInSearchTerms length == 6');

            var query1 = new Parse.Query("SearchTerms");
            query1.contains("searchString", searchTermArray[0]);
            var query2 = new Parse.Query("SearchTerms");
            query2.contains("searchString", searchTermArray[1]);
            var query3 = new Parse.Query("SearchTerms");
            query3.contains("searchString", searchTermArray[2]);
            var query4 = new Parse.Query("SearchTerms");
            query4.contains("searchString", searchTermArray[3]);
            var query5 = new Parse.Query("SearchTerms");
            query5.contains("searchString", searchTermArray[4]);
            var query6 = new Parse.Query("SearchTerms");
            query6.contains("searchString", searchTermArray[5]);
            orQuery = Parse.Query.or(query1, query2, query3, query4, query5, query6);
        } else if (searchTermArray.length == 7) {
            console.log('#findTextInSearchTerms length == 7');

            var query1 = new Parse.Query("SearchTerms");
            query1.contains("searchString", searchTermArray[0]);
            var query2 = new Parse.Query("SearchTerms");
            query2.contains("searchString", searchTermArray[1]);
            var query3 = new Parse.Query("SearchTerms");
            query3.contains("searchString", searchTermArray[2]);
            var query4 = new Parse.Query("SearchTerms");
            query4.contains("searchString", searchTermArray[3]);
            var query5 = new Parse.Query("SearchTerms");
            query5.contains("searchString", searchTermArray[4]);
            var query6 = new Parse.Query("SearchTerms");
            query6.contains("searchString", searchTermArray[5]);
            var query7 = new Parse.Query("SearchTerms");
            query7.contains("searchString", searchTermArray[6]);
            orQuery = Parse.Query.or(query1, query2, query3, query4, query5, query6, query7);
        } else if (searchTermArray.length == 8) {
            console.log('#findTextInSearchTerms length == 8');

            var query1 = new Parse.Query("SearchTerms");
            query1.contains("searchString", searchTermArray[0]);
            var query2 = new Parse.Query("SearchTerms");
            query2.contains("searchString", searchTermArray[1]);
            var query3 = new Parse.Query("SearchTerms");
            query3.contains("searchString", searchTermArray[2]);
            var query4 = new Parse.Query("SearchTerms");
            query4.contains("searchString", searchTermArray[3]);
            var query5 = new Parse.Query("SearchTerms");
            query5.contains("searchString", searchTermArray[4]);
            var query6 = new Parse.Query("SearchTerms");
            query6.contains("searchString", searchTermArray[5]);
            var query7 = new Parse.Query("SearchTerms");
            query7.contains("searchString", searchTermArray[6]);
            var query8 = new Parse.Query("SearchTerms");
            query8.contains("searchString", searchTermArray[7]);
            orQuery = Parse.Query.or(query1, query2, query3, query4, query5, query6, query7, query8);
        } else if (searchTermArray.length == 9) {
            console.log('#findTextInSearchTerms length == 9');

            var query1 = new Parse.Query("SearchTerms");
            query1.contains("searchString", searchTermArray[0]);
            var query2 = new Parse.Query("SearchTerms");
            query2.contains("searchString", searchTermArray[1]);
            var query3 = new Parse.Query("SearchTerms");
            query3.contains("searchString", searchTermArray[2]);
            var query4 = new Parse.Query("SearchTerms");
            query4.contains("searchString", searchTermArray[3]);
            var query5 = new Parse.Query("SearchTerms");
            query5.contains("searchString", searchTermArray[4]);
            var query6 = new Parse.Query("SearchTerms");
            query6.contains("searchString", searchTermArray[5]);
            var query7 = new Parse.Query("SearchTerms");
            query7.contains("searchString", searchTermArray[6]);
            var query8 = new Parse.Query("SearchTerms");
            query8.contains("searchString", searchTermArray[7]);
            var query9 = new Parse.Query("SearchTerms");
            query9.contains("searchString", searchTermArray[8]);
            orQuery = Parse.Query.or(query1, query2, query3, query4, query5, query6, query7, query8, query9);
        } else if (searchTermArray.length >= 10) {
            console.log('#findTextInSearchTerms length == 10');

            var query1 = new Parse.Query("SearchTerms");
            query1.contains("searchString", searchTermArray[0]);
            var query2 = new Parse.Query("SearchTerms");
            query2.contains("searchString", searchTermArray[1]);
            var query3 = new Parse.Query("SearchTerms");
            query3.contains("searchString", searchTermArray[2]);
            var query4 = new Parse.Query("SearchTerms");
            query4.contains("searchString", searchTermArray[3]);
            var query5 = new Parse.Query("SearchTerms");
            query5.contains("searchString", searchTermArray[4]);
            var query6 = new Parse.Query("SearchTerms");
            query6.contains("searchString", searchTermArray[5]);
            var query7 = new Parse.Query("SearchTerms");
            query7.contains("searchString", searchTermArray[6]);
            var query8 = new Parse.Query("SearchTerms");
            query8.contains("searchString", searchTermArray[7]);
            var query9 = new Parse.Query("SearchTerms");
            query9.contains("searchString", searchTermArray[8]);
            var query10 = new Parse.Query("SearchTerms");
            query10.contains("searchString", searchTermArray[9]);
            orQuery = Parse.Query.or(query1, query2, query3, query4, query5, query6, query7, query8, query9, query10);
        }

        console.log('#findTextInSearchTerms searchTermArray', searchTermArray);
        console.log('#findTextInSearchTerms orQuery', orQuery);

        if (typeof(objectType) != typeof(undefined)) {
            orQuery.equalTo("objectTypeName", objectType);
        }

        console.log('#findTextInSearchTerms objectType after');

        motherQuery.matchesQuery(pattern, orQuery);
        motherQuery.limit(1000);
        motherQuery.find({
            useMasterKey: true
        }).then(function(idArray) {

            console.log('#findTextInSearchTerms idArray', idArray);

            //sort objects by number of occurence
            var sortedIds = idArray.map(function(obj) {
                return obj.id;
            });
            // maybe upgrade the suggestions later...
            //_.chain(idArray)
            /*.countBy(function (i) {
          return i.id
        })
        .pairs()
        .sortBy(function (c) {
          return -c[1]
        })
        .map(function (c) {
          return c[0]
        })
        .value();
*/
            promise.resolve(sortedIds);
        }, function(savedObject, error) {
            console.log('findTextInSearchTerms savedObject',savedObject);
            console.log(' findTextInSearchTerms error',error);

            promise.reject(error);
        });
    } else {

        console.log('Inside Else of #findTextInSearchTerms');
        motherQuery.limit(1000);
        motherQuery.find({
            useMasterKey: true
        }).then(function(idArray) {

            var sortedIds = idArray.map(function(obj) {
                return obj.id;
            });

            //sort objects by number of occurence
            /*var sortedIds = _.chain(idArray)
              .countBy(function (i) {
                return i.id
              })    
              .pairs()
              .sortBy(function (c) {
                return -c[1]
              })
              .map(function (c) {
                return c[0]
              })
              .value();*/

            promise.resolve(sortedIds);

        });
    }
    return promise;
}

标签： parse-platformparse-serverback4app

解决方案

这是一个有趣的谜题。我会先给你我的现实世界的建议。然后我会快速回答您如何更改当前模式以支持您在 Parse 中所做的一切，尽管我不推荐这种方法。

现实世界的建议：不要将 parse-server 用于应用程序的这个特定方面！这是一个经典的搜索困境，将其视为这样并使用非常适合此问题的Algolia之类的东西。然后，您将在解析云代码中为您的三个类创建一个 beforeSave 挂钩，并在发布新帖子、回复或评论时更新 Algolia 上的论坛索引，然后您将使用出色的 Algolia 工具为您的应用程序构建 UI 和/或网页。十分简单。您可以免费获得各种花里胡哨，例如构面、评分、自动完成、词干提取、停用词等（如果 Algolia 的成本对您来说是个问题，您可以将 Solr 或弹性搜索视为开源，非商业替代品）。

但是，如果您坚持只为此使用 Parse...删除您的 contetObjectId 并从您的搜索词中输入，并将三个关系列添加到您的搜索词类：论坛、回复、评论和 beforeSave 钩子中的每一个，添加对适当的搜索词关系的帖子、回复或评论。

推荐阅读