I'm currently developing a search function based on tags, within a table structure like this
CREATE TABLE permission (
id serial primary key,
tags varchar(255)[],
);
After adding a row with the tags "artist" and "default," I aim to retrieve data using tag-based queries (employing the knex query builder). Here's an example:
async getByTags(tags: string[]): Promise<PermissionTable[]> {
return this.db<PermissionTable>('permission')
.select('*')
.whereRaw("tags @> '{??}'", [tags])
}
Depending on the number of tags provided, different SQL statements are generated as shown below.
This request functions correctly
select * from "permission" where tags @> '{"artist"}';
However, when querying with multiple tags, it fails to return the desired row and instead yields an empty array.
select * from "permission" where tags @> '{"artist", "event"}';
The issue arises when attempting to query by more than one tag. Why is this happening?