mysql - 尝试使用 knex 并在 where 子句中嵌入 select 来构建复杂查询

Question

我正在尝试将以下 SQL 查询转换为 knex:

SELECT A.sid,
        A.rid,
        A.dataset_name,
        A.jobname,
        A.ndmjob,
        A.ndmprocess,
        A.programs,
        A.version,
        A.backup_dataset,
        A.fxrnum,
        A.`type`,
        A.destination,
        A.frequency,
        A.snode,
        A.runtask,
        A.clientDSN,
        A.comments
    FROM filedesigner.fileSpecification A
    WHERE A.file_id = var_fileid AND
          A.version = (
     SELECT MAX(B.version) 
       FROM filedesigner.fileSpecification B
      WHERE B.sid = A.sid
        AND B.version <= var_version)
    ORDER BY A.rid DESC, 
             A.dataset_name;

我目前在我的 knex 查询解析器中有这个:

this.db
      .select(
        'A.sid as specId',
        'A.version',
        'A.rid',
        'A.dataset_name as datasetName',
        'A.jobname as jobName',
        'A.ndmjob as ndmJob',
        'A.ndmprocess as ndmProcess',
        'A.backup_dataset as backupDataset',
        'A.programs',
        'A.fxrnum as fxrNum',
        'A.type',
        'A.destination',
        'A.frequency',
        'A.snode',
        'A.runtask',
        'A.clientDSN',
        'A.comments'
      )
      .from('fileSpecification as A')
      .where('file_id', fileId)
      .andWhere('version', () => this.db
        .max(
          'B.version'
        )
        .from('fileSpecification as B')
        .where('A.sid', 'B.sid')
        .andWhere('B.version', '<=', version)
        .then(data => data[0]))
      .orderBy('sid', 'asc')
      .orderBy('version', 'desc')
      .orderBy('rid', 'desc')
      .orderBy('dataset_name', 'asc')
      .cache(ttl)
      .then(data => data);

但是我收到了这个错误:(变量是 fileId=33 和 version='2.0.1')

select `A`.`sid` as `specId`, `A`.`version`, `A`.`rid`, `A`.`dataset_name` as `datasetName`, `A`.`jobname` as `jobName`, `A`.`ndmjob` as `ndmJob`, `A`.`ndmprocess` as `ndmProcess`, `A`.`backup_dataset` as `backupDataset`, `A`.`programs`, `A`.`fxrnum` as `fxrNum`, `A`.`type`, `A`.`destination`, `A`.`frequency`, `A`.`snode`, `A`.`runtask`, `A`.`clientDSN`, `A`.`comments` from `fileSpecification` as `A` where `file_id` = 33 and `version` = (select *) order by `sid` asc, `version` desc, `rid` desc, `dataset_name` asc - ER_NO_TABLES_USED: No tables used

我尝试了几种变体，它们都给了我相同的结果，甚至更糟。任何帮助将不胜感激!

Answer 1

虽然在没有示例数据的情况下很难对此进行测试，但这里是 SQL 的 Knex 翻译:

const version = db
  .max("B.version")
  .from("filedesigner.fileSpecification as B")
  .where("B.sid", "A.sid")
  .andWhere("B.version", "<=", var_version);

db.select(
  "A.sid",
  "A.rid",
  "A.dataset_name",
  "A.jobname",
  "A.ndmjob",
  "A.ndbprocess"
  // etc
)
  .from("filedesigner.fileSpecification as A")
  .where("A.file_id", var_fileid)
  .andWhere("A.version", version)
  .orderBy([{ column: "A.rid", order: "desc" }, "A.dataset_name"])
  .then(console.log)
  .catch(console.error)

请注意，子查询上没有 .then 调用。您将其留给 Knex 来管理...您根本不想等待 promise 。我在末尾留下了 console.log，以便更明显地了解 .then 的去向。