Project: codever - File: 1586782890001_add-codelets-indexes-DONE.js

Full text search is supported in Mongo by using a text index. Text indexes can include any field whose value is a string, or an array of string elements, to which you can give weights. For a text index, the weight of an indexed field denotes the significance of the field relative to the other indexed fields in terms of the text search score.

db.snippets.createIndex(
  {
    title: "text",
    tags: "text",
    "codeSnippets.comment": "text",
    "codeSnippets.code": "text",
    sourceUrl: "text"
  },
  {
    weights: {
      title: 8,
      tags: 13,
      "codeSnippets.comment": 3,
      "codeSnippets.code": 1,
      sourceUrl: 1
    },
    name: "full_text_search",
    default_language: "none",
    language_override: "none"
  }
);

For each indexed field in the document, MongoDB multiplies the number of matches by the weight and sums the results. Using this sum, MongoDB then calculates the score for the document. You then can use the $meta operator for details on returning and sorting by text scores, as in the snippet below:

let getPublicBookmarksForSearchedTerms = async function (nonSpecialSearchTerms, page, limit, sort, specialSearchFilters, searchInclude) {

  let filter = {
    public: true
  }

  if ( nonSpecialSearchTerms.length > 0 ) {
    if(searchInclude === 'any') {
      filter.$text = {$search: nonSpecialSearchTerms.join(' ')}
    } else {
      filter.$text = {$search: bookmarksSearchHelper.generateFullSearchText(nonSpecialSearchTerms)};
    }
  }

  addSpecialSearchFiltersToMongoFilter(specialSearchFilters, filter);

  let sortBy = {};
  if ( sort === 'newest' ) {
    sortBy.createdAt = -1;
  } else {
    sortBy.score = {$meta: "textScore"}
  }

  let bookmarks = await Bookmark.find(
    filter,
    {
      score: {$meta: "textScore"}
    }
  )
    .sort(sortBy)
    .skip((page - 1) * limit)
    .limit(limit)
    .lean()
    .exec();

  return bookmarks;
}

Reference - https://docs.mongodb.com/manual/core/index-text/

Shared with from Codever.land. 👉 Use the Copy to mine functionality to copy this snippet to your own personal collection and easy manage your code snippets.

Project: codever - File: personal-bookmarks-search.service.js

To perform a text search query on the text index of a collection, you need to use the $text operator. In the example below the results are sorted in order of relevance score, which you must explicitly project to the $meta textScore field and sort on it:

let getPersonalBookmarksForSearchedTerms = async function (nonSpecialSearchTerms, page, limit, userId, specialSearchFilters, searchInclude) {

  let filter = {userId: userId};
  if ( nonSpecialSearchTerms.length > 0 ) {
    if ( searchInclude === 'any' ) {
      filter.$text = {$search: nonSpecialSearchTerms.join(' ')}
    } else {
      filter.$text = {$search: bookmarksSearchHelper.generateFullSearchText(nonSpecialSearchTerms)};
    }
  }
  addSpecialSearchFiltersToMongoFilter(specialSearchFilters, filter);

  let bookmarks = await Bookmark.find(
    filter,
    {
      score: {$meta: "textScore"}
    }
  )
    .sort({score: {$meta: "textScore"}})
    .skip((page - 1) * limit)
    .limit(limit)
    .lean()
    .exec();

  return bookmarks;
}

By default, the full text search in Mongo looks for any of the terms, so a query of “java exception” will look for the bookmarks that container java or exception. You can force an AND search by using the terms in double quotes, so the query text would be "java" "exception". This is what the following generateFullSearchText function does for terms that you do not want excluded (To exclude a word in mongo, you can prepend a “-” character):

let generateFullSearchText = function (nonSpecialSearchTerms) {
  let termsQuery = '';
  nonSpecialSearchTerms.forEach(searchTerm => {
    if ( searchTerm.startsWith('-') ) {
      termsQuery += ' ' + searchTerm;
    } else { //wrap it in quotes to make it a default AND in search
      termsQuery += ' "' + searchTerm.substring(0, searchTerm.length) + '"';
    }
  });

  return termsQuery.trim();
};

Reference - https://docs.mongodb.com/manual/text-search/

Shared with from Codever.land. 👉 Use the Copy to mine functionality to copy this snippet to your own personal collection and easy manage your code snippets.

Project: codever - File: personal-bookmarks-search.service.js

Use the mongo cursor skip method with offset in combination with the limit method:

let getPersonalBookmarksForSearchFilter = async function (filter, pageNumber, nPerPage) {

  let bookmarks = await Bookmark.find(
    filter,
    {
      score: {$meta: "textScore"}
    }
  )
    .sort({score: {$meta: "textScore"}})
    .skip(pageNumber > 0 ? ((pageNumber - 1) * nPerPage) : 0)
    .limit(nPerPage)
    .lean()
    .exec();

  return bookmarks;
}

Reference - https://docs.mongodb.com/manual/reference/method/cursor.skip/

Shared with from Codever.land. 👉 Use the Copy to mine functionality to copy this snippet to your own personal collection and easy manage your code snippets.

Project: codever - File: personal-bookmarks.service.js

Use the sort and limit methods. In the following example we receive the latest 30 (limit) created bookmarks (sort descending by createdAt date) :

let getLastCreatedBookmarks = async (userId) => {
  const bookmarks = await Bookmark.find({userId: userId})
    .sort({createdAt: -1}) // -1 for descending sort
    .limit(30);

  return bookmarks;
};

Reference - https://docs.mongodb.com/manual/reference/method/cursor.sort/

Shared with from Codever.land. 👉 Use the Copy to mine functionality to copy this snippet to your own personal collection and easy manage your code snippets.

Suppose we have the following table, and we often execute queries to display the bookmarks belonging to a certain category:

CREATE TABLE bookmark (
    id           NUMBER(10, 0), -- number 10 digits before the decimal and 0 digits after the decimal
    title        VARCHAR2(255 CHAR) NOT NULL, -- String with a maximum length of 255 charachters
    url          VARCHAR2(500 CHAR) UNIQUE NOT NULL, -- holds unique values across the table data
    category     VARCHAR2(500 CHAR) NOT NULL, -- holds unique values across the table data
    is_public    NUMBER(1, 0) NOT NULL, -- plays the role of a boolean '0'-false, '1'-true ,
    created_at   DATE NOT NULL, --  when the entry is created
    PRIMARY KEY ( id )
);

Then an index on the column category would make the query more performant. To create it, issue the following command (creates by default a B-Tree index):

CREATE INDEX category_i ON bookmark (category);

--confirm its creation by querying the existing index on the table
SELECT * FROM all_indexes WHERE table_name = 'BOOKMARK';

Now, maybe retrieving the latest bookmarks with a category is also a common scenario, so it might be worth creating then an index on multiple columns, in our case category and created_at. The command would look something like the following:

CREATE INDEX category_created_at_i ON bookmark (category, created_at DESC);

-- this would be a perfect fit for the following query
SELECT * FROM bookmark WHERE category='blog' ORDER BY created_at DESC;

To delete the created indexes use the DROP INDEX commands:

DROP INDEX category_i;
DROP INDEX category_created_at_i;

Reference - https://blogs.oracle.com/sql/how-to-create-and-use-indexes-in-oracle-database

Shared with from Codever.land. 👉 Use the Copy to mine functionality to copy this snippet to your own personal collection and easy manage your code snippets.