My database backend is PostgreSQL and I have a TypeORM object simplified as follows:
@Entity()
@Index(['name'], {unique: true})
export class Foo extends BaseEntity {
@PrimaryGeneratedColumn('uuid')
id: string;
@Column()
name: string;
@Column('varchar', { array: true })
bar: string[];
}
I am currently building an API query handler to handle searches. I can easily perform a LIKE query on the name attribute like this:
let qs = Foo.createQueryBuilder('foo');
qs.andWhere('foo.name ILIKE :name', {
name:'%${name}%'
});
However, I also want to be able to search for any "bar" value that contains a specific query term using a soft comparison method. Despite finding documentation on how to exact match a search term in the "bar" attribute, I cannot find information on performing a soft comparison.
The main goal is to allow users to enter a specific query, such as "%oo%", and retrieve the first two records where the "bar" strings contain that substring. For example, given the following dataset:
[
{id: 1, name: 'whatever', bar: ['apple','banana','yeti','woo']},
{id: 2, name: 'something else', bar: ['red','blue','green', 'boo']},
{id: 3, name: 'i dunno', bar: ['ford','chevy']},
]
Users should be able to query "%oo%" and receive the first two records based on the presence of that substring within the "bar" values.