ResolvedDatabasesWeb

[SOLVED] How to achieve a LIKE query in the database?

Dec 27, 2023, 20:50

View on Discord

Votes

Replies

Participants

Unknown

Messages

MajekOriginal post

Rank 1: Thread

Jun 10, 2023, 12:49

How can I execute a search query like this on the database?

SELECT * FROM <COLLECTION> WHERE <ATTRIBUTE1>="exact string" AND <ATTRIBUTE2> LIKE "%some string%"

Summary

Title: [SOLVED] How to achieve a LIKE query in the database? Solution: To achieve a LIKE query in the database, use a full text index and perform a prefix search with the searched query. Create 2 full text indexes: one with both attributes and another with just the search attribute. Here's a code snippet: ``` const spots = await database.listDocuments( process.env.NEXT_PUBLIC_DATABASE, process.env.NEXT_PUBLIC_FOOD_SPOT, [ Query.search("areaId", areaId), Query.search("foodSpotName", search), Query.orderDesc("ratings"), Query

Majek

Rank 1: Thread

Jun 10, 2023, 23:08

I tried this but ATTRIBUTE2 needs to be an exact match string.

Plain text

Query.equal('ATTRIBUTE1',['exact string']),
Query.equal('ATTRIBUTE2',['exact string'])
]```

I have a key index on `ATTRIBUTE1` and `ATTRIBUTE2`.

Also tried this approach, I combined the values of  `ATTRIBUTE1` and `ATTRIBUTE2` into one attribute resulting to `ATTRIBUTE3` and make a fulltext index on it. The problem is it is using an OR logic

I also tried to have a fulltext index for both  `ATTRIBUTE1` and `ATTRIBUTE2` (while the key index for each still exists) and implement this code below but it is returning a `Server Error`.
```[
Query.equal('ATTRIBUTE1','exact string'),
Query.search('ATTRIBUTE2','some string')
]```

Drake

Admin

Jun 10, 2023, 23:55

You can't do a substring match at the moment. The closing thing you can do is a prefix search with the search query and a full text index

Majek

Rank 1: Thread

Jun 11, 2023, 00:17

Oh ok, can you provide maybe some sample code snippets on how to do this? Thank you for patiently replying to us.

Drake

Admin

Jun 11, 2023, 00:26

Create 2 full text indexes: 1 with both attributes and then 1 with just the search attribute

Majek

Rank 1: Thread

Jun 11, 2023, 00:38

oh ok let me try that.

Majek

Rank 1: Thread

Jun 11, 2023, 01:06

these are my index

Majek

Rank 1: Thread

Jun 11, 2023, 01:06

and this is my code. I am getting a server error.

const spots = await database.listDocuments(
    process.env.NEXT_PUBLIC_DATABASE,
    process.env.NEXT_PUBLIC_FOOD_SPOT,
    [
      Query.search("areaId", areaId),
      Query.search("foodSpotName", search),
      Query.orderDesc("ratings"),
      Query.limit(10),
      Query.offset(10 * pageNumber),
    ]
  );```

Majek

Rank 1: Thread

Jun 11, 2023, 01:15

I figured it out, changed my code into this