Back
[SOLVED] How to achieve a LIKE query in the database?
How can I execute a search query like this on the database?
SELECT * FROM <COLLECTION> WHERE <ATTRIBUTE1>="exact string" AND <ATTRIBUTE2> LIKE "%some string%"
TL;DR
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 I tried this but ATTRIBUTE2 needs to be an exact match string.
TypeScript
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')
]```
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
Oh ok, can you provide maybe some sample code snippets on how to do this? Thank you for patiently replying to us.
Create 2 full text indexes: 1 with both attributes and then 1 with just the search attribute
oh ok let me try that.
these are my index
and this is my code. I am getting a server error.
TypeScript
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),
]
);```
I figured it out, changed my code into this
TypeScript
const spots = await database.listDocuments(
process.env.NEXT_PUBLIC_DATABASE,
process.env.NEXT_PUBLIC_FOOD_SPOT,
[
Query.equal("areaId", areaId),
Boolean(search)
? Query.search("foodSpotName", search)
: Query.equal("areaId", areaId),
Query.orderDesc("ratings"),
Query.limit(10),
Query.offset(10 * pageNumber),
]
);```
[SOLVED] How to achieve a LIKE query in the database?
