TypeORM Postgres WHERE ANY or IN With QueryBuilder Or Find?

Question

I've been trying to figure out how to select every record in a Postgres db that includes a certain id in a column that has arrays of integers. I'm new at all this but I believe I want an SQL statement that looks like this:

SELECT * FROM members WHERE 2 = ANY(skill_id_array);

The data in the skill_id_array column looks like this: {1,4,7}.

From the Angular front-end I pass in an id such as 2 as a parameter. The logging in terminal shows the end of the select statement as this but it fails:

... WHERE $1 ANY (skill_id_array) -- PARAMETERS: [2]

I believe the issue is I'm trying to pass a variable into the code and it hates it. Notice that I've been trying the find method and QueryBuilder. Resulting errors are in the comments. (Full CRUD is working so my overall setup is fine.)

This works great but I understand this has a sql injection problem:

const membersBySkill = await this.entityManager.query(
      `SELECT * FROM members WHERE ${integerId} = ANY(members.skill_id_array)`
    );

This works but I'm not sure of a sql injection problem plus I would like to use TypeORM find or QueryBuilder.

  const sql = 'SELECT * FROM members WHERE '+ integerId + ' = ANY(skill_id_array)';
  const membersBySkill = await this.entityManager.query(sql);

In my service:

import {Any} from "typeorm";

  async getMembersBySkill(id) {
    const integerId = parseInt(id, 10); // convert id to integer for Postgres array.
    // console.log('skill_id in service', integerId);
    // Find one skill id in an array of id's.
/*
    const membersBySkill = await this.connection.getRepository(Members).find({
      skill_id_array: Any(integerId)
    });
    //  This results in an IDE error.  It doesn't like a number as 
    //  the ANY param.  "{integerId: number} is not assignable to
    //  parameter type '{}[] | FindOperator<{}>'.
    //  In terminal: error: could not find array type for data type integer[].  
    //  However, the db has integers.
*/

    const membersBySkill = await getRepository(Members)
      .createQueryBuilder("members")
      .select('*')
      .where(":id IN (skill_id_array)", {id: integerId})
      .getMany();
      // SELECT * FROM "members" "members" WHERE $1 IN (skill_id_array) -- PARAMETERS: [2]
      // server console: error: malformed array literal: "2"

    console.log('membersBySkill: ', membersBySkill);
    return membersBySkill;
  }

The entity:

  @Column('int', { array: true, nullable: true})
  skill_id_array: number[];

The Postgres column type: integer[]

I stop and start the Nestjs server with every change.

Answer 1

currently it is possible with syntax

.where("id IN(:...ids)", { ids: [1,2,3] })

Answer 2

If you use OOP

const list = await this.repository.find({ where: { id: In([...idArr]) } });

Answer 3

Many places on the Web say that the SQL operators IN and ANY are almost equivalent. So I was lead in the direction of thinking they are setup almost the same except that ANY requires '='. Wrong, but it took a long time to figure that out.

This ANY setup in QueryBuilder works:

const membersBySkill = await getRepository(Members)
      .createQueryBuilder()
      .where(':id = ANY (skill_id_array)', {id: integerId})
      .getMany();

Previously I was using the 'Any' setup from the TypeORM Find Options doc because ANY isn't addressed (yet) in the QueryBuilder doc. It seemed like the way to go at the time but don't mix them.

TypeORM lead contributor @pleerock suggested using an alias with this setup, so here is his example for reference with the alias 'member':

const results = await getRepository(Members)
    .createQueryBuilder("member") // you shall assign an alias
    .where(":id = ANY(member.skill_id_array)", { id: 1 }) // and use that alias everywhere in your query builder
    .getMany();

So what about IN?

The issue is you can't use a variable with IN like you can with ANY. It will never work. It requires a 'list of scalars' instead of an expression is how the docs put it. My var contains a list of scalars but the IN param must have a list, not a reference to a list.

IN (id1, id2, id5) should work and you need to populate those vars elsewhere with either integers or strings. For my app that means parsing my skill_id_array and assigning the individual integers to their own unique vars. This is a lot of work when ANY will easily accomplish the same result.