Filter data by multiple Query Params when object values have different types-CodePudding

I've been trying for days to get this thing to work, and I feel like I have the pieces but I don't know what I am missing.

My object looks like this when it goes to my service:

GetAllDataFilterDto {
  orgId: 12345,
  status: [ 'approved', 'pending' ],
}

I am using typeorm, this is my query:

  protected getAll(filter: GetAllDataFilter): SelectQueryBuilder<EntityName> {
    const qb = Repository<EntityName>
      .createQueryBuilder('entityName')
      .select([
        'entityName.id',
        'entityName.status',
        'entityName.orgId',
      ])
      .groupBy('entityName.id')
      .where(this.getFilterConditions(filter));

    return qb;
  }

My query uses getMany() but in a different file which is why it's not included on this function.

Here's where I am having the issue, my current filter function looks like this:

  protected getFilterConditions(filter: GetAllDataFilter) {
    return _.pickBy(filter, (value, key) => value !== undefined && this.entityProperties.has(key));
  }

entityProperties is a set that looks like this:

Set {
'id',
'status',
'orgId'
}

Currently, this filter works for numbers and if my array only has one value, but when I add two values then it returns the error

trace: QueryFailedError: Operand should contain 1 column(s)

My end goal is to filter my returned data even if there are optional parameters, so all status columns with approved or pending will be returned, but I still need to be able to filter by orgId at the same time.

Please let me know if there is more I should include, thank you.

CodePudding user response：

I think the problem might be with .where on your queryBuilder, its suppose to take 1 parameter(in other words to check the condition of 1 field only), and then you can add .andWhere to add filter on 1 more parameter.

From the docs :

for single .where condition:

Adding a WHERE expression is as easy as:
createQueryBuilder("user").where("user.name = :name", { name: "Timber" })

for multiple .where conditions:

You can add AND into an existing WHERE expression:

createQueryBuilder("user")
    .where("user.firstName = :firstName", { firstName: "Timber" })
    .andWhere("user.lastName = :lastName", { lastName: "Saw" })