[loopbackjs]how to do where not in (select xxx) query

Question

I'd love to use loopback 4 whereBuilder to build a query like 'select * from file where id not in (select fileId from workFile where workId='xxxxx')' and I tried this:

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

and

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

but both of them are wrong , what should i do ? this is the error:

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: {

the correct format is like this:

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

but if i change to

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

its wrong, i don't know if someone know and use this framework

Answer 1

i found the solution , first i fetch the included ids:

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

and then i fetch like this:

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