My backend database is PostgreSQL I have a TypeORM object simplified to:

@Entity()
@Index(['name'], {unique: true}
export class Foo extends BaseEntity 
{
 @PrimaryGeneratedColumn('uuid')
  id: string;


  @Column()
  name: string;

  @Column('varchar', { array: true })
  bar: string[];
}

I'm creating an API query handler that can handle searches. I can easily do a LIKE query on the name like this:

 let qs = Foo.createQueryBuilder('foo');
 qs.andWhere('foo.name ILIKE :name', {
    name:'%${name}%'
  });

I'd like to also search for e.g. any "bar" LIKE %myqueryterm% but I can't seem to find anything on that.

I see a bunch of docs on how to exactly match a search term in bar, but no soft comparison.

What I essentially want to do is that I have a data set

[
{id: 1, name: 'whatever', bar: ['apple','bananna','yeti','woo']},
{id: 2, name: 'something else', bar: ['red','blue','green', 'boo']},
{id: 3, name: 'i dunno', bar: ['ford','chevy']},
]

and I'd like to let the user to be able to query e.g. "%oo% and return the first 2 records based on bar strings containing that substring.

CodePudding user response：

I don't know typeorm. but based on https://github.com/typeorm/typeorm/issues/881

The sql query would be like:

WITH cte (
    id,
    name,
    bar
) AS (
    VALUES (1, 'whatever', ARRAY['apple', 'bananna', 'yeti', 'woo']),
        (2, 'something else', ARRAY['red', 'blue', 'green', 'boo']),
        (3, 'i dunno', ARRAY['ford', 'chevy'])
),
cte1 AS (
    SELECT
        json_agg(row_to_json(cte.*)) AS json_all
    FROM
        cte
)
SELECT
    value
FROM
    cte1,
    json_array_elements(json_all)
WHERE
    value ->> 'bar' ~ 'oo';

Based on the github page, it would be like:

getConnection().query("
with cte(id,name,bar) as (values
 (1,'whatever',array ['apple','bananna','yeti','woo'])
,(2,'something else',array  ['red','blue','green', 'boo'])
,(3,'i dunno',array ['ford','chevy'])
),cte1 AS
(select json_agg(row_to_json(cte.*)) as json_all from cte)
select value
from cte1,json_array_elements(json_all)
where value->>'bar' ~ @0", ['oo']);

case insensitively match would be value->>'bar' ~* @0"

CodePudding user response：

Postgres provides array functions and operators that you can use to create any complex query.

In your case, a clean way of doing this would be to

1. Convert the array to a string and then
2. Perform the LIKE operation on that string

Something like this should work:

.createQueryBuilder('foo')
.where("array_to_string(foo.bar, ',') LIKE :bar", {
 bar: '