Complex search between more than one tables is not working in Parse Server?

Question

I have 4 tables:

ForumPost
ForumReply
ForumComment
SearchTerms

Relation between tables :

SearchTerms Table Fields :

objectId
ACL
SearchString : This column contains a string or related object (ForumPost, ForumComment, ForumReply) we will use this column to search any object or related table
objectTypeName : This contains name of reference table (ForumPost, ForumComment, ForumReply)
connectedObjectId : This contains a objectId of related object from table ForumPost, ForumComment, ForumReply

ForumPost Table Fields:

objectId
replies :(Array) This field contains object of all ForumReply in array (Sample attached)
ACL
title
description

//Sample of replies column

    [
      {
        "__type": "Pointer",
        "className": "ForumReply",
        "objectId": "LSVO4KwHxO"
      },
      {
        "__type": "Pointer",
        "className": "ForumReply",
        "objectId": "EQA9Fotvp5"
      },
      {
        "__type": "Pointer",
        "className": "ForumReply",
        "objectId": "smpfWT8fbq"
      }
    ]

ForumReply Table Fields:

objectId
text : Description of reply
to: This is a pointer of ForumPost table
searchTerm: This is a pointer of searchTerms table

comments: (Array) This field contains object of all ForumComment in array (Sample attached)

//Sample of comments field

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


]

ForumComment Table Fields:

objectId
text : Description of reply
to: This is a pointer of ForumReply table
searchTerm: This is a pointer of searchTerms table

What's my goal: I have an web form where user enter a search string I want to get all ForumPost who have this string. Search will apply in ForumnReply and ForumCommentstoo. So the posts whose reply and comment have string will be also in result.

What I did: : First I run search in SearchTerms table (because this table have search string of all the tables ForumPost, ForumReply,ForumComment, so now I have ids of all matched objects (ForumPost, ForumComment, ForumReply). After this I tried to use OR query with resultant ids (using containedIn) and also tried to use Aggregates but none is returning all matched posts.

Please suggest if there is an better way to run this complex search.

Here is my Code:

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

Can you share your code ? – Suat Karabacak Mar 01 '20 at 20:05 — Suat Karabacak, Mar 01 '20 at 20:05

score 0 · Answer 1 · answered Mar 05 '20 at 22:08

This is an interesting puzzle. I'll give you my real-world suggestion first. And then I'll give you a quick answer on how to change your current schema to support what you are doing all in Parse, though I don't recommend that approach.

The real-world suggestion: don't use parse-server for this particular aspect of your application! This is a classic search dilemma, treat it as such and use something like Algolia that is very well suited for this problem. You would then create a beforeSave hook in parse cloud code for your three classes and update a Forum index on Algolia whenever a new post, response or comment is made, and then you would use the excellent Algolia tools to build a UI for your app and/or web page. easy peasy. There are all kinds of bells and whistles you get for free like facets, scoring, autocomplete, stemming, stop words, etc. (if the cost of Algolia is an issue for you, you could look at Solr or elastic search as open-source, non-commercial alternatives).

But, if you insist on using just Parse for this...drop your contetObjectId and type from your search term and add three relations columns to your search term class: forum, reply, comment and in the beforeSave hooks for each of those, add the post, reply, or comment to the appropriate search term relation.

Complex search between more than one tables is not working in Parse Server?

1 Answers1