Improve search query possibilities

[ondratra]

Currently, queries searching text fields have limited capabilities.

The following query finds records that have their text exactly test, but doesn't find records that contain the text as a substring e.g. mytest.

query {
  search(text: "test") {
    item {
      __typename
      ... on Channel {
        title
      }
      ... on Video {
        title
      }
    }
    rank
  }
}

Research more about current limits of search for a text. If possible, create a feature that will enable searching for both an exact word or part of a string.

[ondratra]

After more testing, it seems that the search for test will find mytest, but some irregularities were experienced.

When tried on sumer-dev2 server, SQL query created for the GraphQL query described above suspiciously returned the same value for Postgres ts_rank, which is odd. Also, when changing the searched text to search(text: "test 8")... it wrongly finds some a video that has title cat2. We need to dig a little bit deeper to find the cause of these troubles.

About features/limitation:
For each such fulltext query, there are 2 files generated from a template by Hydra XXXqueryNameXXX.resolver.ts and XXXqueryNameXXX.service.ts. That gives us a lot of space if we need to change the behavior since we are creating SQL queries in .service.ts file.

In input schema, we can mark a property as fulltext searchable by @fulltext directive title: String @fulltext(query: "search"). We can have multiple fulltext queries e.g. one for searching in video and channel titles and another for searching category names.

We can use the filtering directives whereXXXentityNameXXX for fulltext as we do for regular entities e.g. search(text: "test", whereVideo: {channelId_eq: 8}) {...}. Currently, one property can be part of only one fulltext search (title: String @fulltext(query: "search") @fulltext(query: "mySecondSearch") will not work - see #457).

[ondratra]

In the light of recent discoveries described in Joystream/joystream#2800 we can consider creating a brand new schema directive (e.g. @ourNewSearch similarly to what's described in #457) and start from scratch to get rid of problematic inner workings of @fulltext. This may or may not be a good approach and should be decided after further inspection of the cause of problems described in Joystream/joystream#2800.

[dmtrjsg]

Needed for ordering results by relevance

Pioneer - Forum
Atlas - search for videos/ channels