代码之家 › 专栏 › 技术社区 › Nditah

如何使用迭代器和Async await查询googlecloud panner表T1,同时为T1中的每条记录查询第二个表T2

google-cloud-spanner es6-promise iterator async-await sql

1

Nditah · 技术社区 · 7 年前

我在googlecloud-panner数据库中有两个表-Authors和Books。

常量请求={ 架构:[ CREATE TABLE Authors ( AuthorId INT64 NOT NULL, FirstName STRING(1024), LastName STRING(1024), AuthorInfo BYTES(MAX) ) PRIMARY KEY (AuthorId) CREATE TABLE Books ( AuthorId INT64 NOT NULL, BookId INT64 NOT NULL, BookTitle STRING(MAX) ) PRIMARY KEY (AuthorId, BookId), INTERLEAVE IN PARENT Authors ON DELETE CASCADE , ], };

export async function findAuthorBooks(authorId) {
  // [START spanner_find_author_books]
  const database = instance.database(databaseId);
  const query = {
    sql: "SELECT * FROM Books As t WHERE t.AuthorId = @authorId",
    params: { authorId },
    types: { authorId: "string" },
  };

  const results = await database.run(query);
  const rows = results[0];
  const result = [];
  rows.forEach((row) => {
    const json = row.toJSON();
    result.push(json);
  });
  database.close();
  if (Array.isArray(result)) return result;
  throw new Error("err");
  // [END spanner_find_author_books]
}

export async function findAuthors1() {
  // [START spanner_find_authors]
  const database = instance.database(databaseId);
  const results = await database.run({ sql: "SELECT * FROM Authors" });
  const rows = results[0];
  const result = [];
  for (const row of rows) {
    const json = row.toJSON();
    const id = json.vendorId;
    json.notification = await findAuthorBooks(id);
    result.push(json);
  }
  database.close();
  if (Array.isArray(result)) return result;
  throw new Error("err");
  // [END spanner_find_authors]
}

但这里的问题是,迭代器/生成器需要再生器运行时,这对于本指南来说太重了,不允许再生器运行时。另外,应该避免循环,以利于数组迭代(根据Eslint)。

export async function findAuthors2() {
  // [START spanner_find_authors]
  const database = instance.database(databaseId);
  const results = await database.run({ sql: "SELECT * FROM Authors" });
  const rows = results[0];
  const result = [];

  await Promise.all(rows.map(async (row) => {
    const json = row.toJSON();
    const id = json.vendorId;
    json.notification = await findAuthorBooks(id);
    result.push(json);
  }));
  database.close();
  if (Array.isArray(result)) return result;
  throw new Error("err");
  // [END spanner_find_authors]
}

不幸的是,它不起作用。那么,我如何才能让它工作,或者有一个不同的(更好的)方式做它,而不必使用承诺? 或者

1 回复 | 直到 7 年前

1

2

RedPandaCurios 7 年前

或者有没有一种方法可以编写一个子查询来选择图书作为数组并添加到选择作者的主查询中?

(见 Notes about Subqueries 在扳手SQL文档中)

SELECT 
  a.AuthorId, 
  a.FirstName, 
  a.LastName, 
  a.AuthorInfo,
  ARRAY(SELECT AS STRUCT
          b.BookID, b.BookTitle
        FROM
          Books b
        WHERE
          a.AuthorId = b.AuthorId) as Books
FROM
  Authors a;

还有一种更简单的方法:只需将这两个表连接起来,并按作者/书籍组合获取一行,然后在authord更改时在代码中进行检测

SELECT 
  a.AuthorId, 
  a.FirstName, 
  a.LastName, 
  a.AuthorInfo,
  b.BookID,
  b.BookTitle
FROM
  Authors a, Books b
WHERE
  a.AuthorID = b.BookID
ORDER BY
  a.AuthorID;