[loopbackjs]如何执行where not in (select xxx)查询

Question

我喜欢使用loopback 4 whereBuilder来构建一个查询，比如'select * from file where id not in (select fileId from workFile where workId='xxxxx')‘，我试了一下：

const fileIdArray = await this.workFileRepository.find({
      where: {
        fileId: {
          eq: xxx
        }
      }
    });
    const ret = await this.fileRepository.find({
      where: {
        id: {
          nin: fileIdArray
        }
      }
    })

和

const ret = await this.fileRepository.find({
      where: {
        id: {
          nin: () => {
            return await this.workFileRepository.find({
              where: {
                fileId: {
                  eq: id
                }
              }
            })
          }
        }
      }
    })

但是他们两个都错了，我该怎么办？这是错误：

error TS2345: Argument of type '{ where: { id: { nin: () => (WorkFile & WorkFileRelations)[]; }; }; }' is not assignable to parameter of type 'Filter<File>'.
  Types of property 'where' are incompatible.
    Type '{ id: { nin: () => (WorkFile & WorkFileRelations)[]; }; }' is not assignable to type 'Condition<File> | AndClause<File> | OrClause<File> | undefined'.
      Type '{ id: { nin: () => (WorkFile & WorkFileRelations)[]; }; }' is not assignable to type 'Condition<File>'.
        Types of property 'id' are incompatible.
          Type '{ nin: () => (WorkFile & WorkFileRelations)[]; }' is not assignable to type 'string | (string & Date) | PredicateComparison<string> | undefined'.
            Type '{ nin: () => (WorkFile & WorkFileRelations)[]; }' is not assignable to type 'PredicateComparison<string>'.
              Types of property 'nin' are incompatible.
                Type '() => (WorkFile & WorkFileRelations)[]' is missing the following properties from type 'string[]': pop, push, concat, join, and 25 more.

 86     const ret = await this.fileRepository.find({
                                                   ~
 87       where: {

正确的格式如下：

const ret = await this.fileRepository.find({
      where: {
        id: {
          nin: ["xxxxxx2","xxxxxx3"]
        }
      }
    })

但如果我改成

const ret = await this.fileRepository.find({
      where: {
        id: {
          nin: ()=>{
            return ['xxxxx2','xxxx3']
          }
        }
      }
    })

这是错误的，我不知道是否有人知道并使用这个框架

Answer 1

我找到了解决方案，首先获取包含的ids：

const inqFileCollectionss = await this.workFileRepository.find({
      where: {
        workId: {
          eq: id
        }
      }
    });
    const inqFileIds = inqFileCollectionss.map(item => item.fileId)

然后我像这样取回：

 const data = await this.fileRepository.find({
      where: {
        id: {
          nin: inqFileIds
        }
      },
      limit: pageSize,
      skip: pageIndex
    })