javascript - 有什么方法可以让两个 javascript 数组的内容变成 "join"，就像我在 SQL 中做一个连接一样

Question

我有两个数组:Question 和 UserProfile

• userProfiles:[] 数组包含 { id, name } 对象
• 问题:[] 数组包含{ id, text, createdBy } 对象

问题中的 createdBy 整数始终是 userProfiles 中的 id 值之一。

有没有一种方法可以像使用数据库时连接两个 SQL 表一样“连接”数组。

最终我需要的是一个包含

{ id, text, name }

对应的 SQL 是:

SELECT u.id, q.text, u.name 
FROM userProfiles u 
JOIN questions q ON q.createdBy=u.id

Answer 1

我想你想要的是 inner join ，这很简单，可以用 JavaScript 实现:

const innerJoin = (xs, ys, sel) =>
    xs.reduce((zs, x) =>
    ys.reduce((zs, y) =>        // cartesian product - all combinations
    zs.concat(sel(x, y) || []), // filter out the rows and columns you want
    zs), []);

出于演示目的，我们将使用以下数据集(感谢@AshokDamani):

const userProfiles = [
    {id: 1, name: "Ashok"},
    {id: 2, name: "Amit"},
    {id: 3, name: "Rajeev"},
];

const questions = [
    {id: 1, text: "text1", createdBy: 2},
    {id: 2, text: "text2", createdBy: 2},
    {id: 3, text: "text3", createdBy: 1},
    {id: 4, text: "text4", createdBy: 2},
    {id: 5, text: "text5", createdBy: 3},
    {id: 6, text: "text6", createdBy: 3},
];

这是你将如何使用它:

const result = innerJoin(userProfiles, questions,
    ({id: uid, name}, {id, text, createdBy}) =>
        createdBy === uid && {id, text, name});

在 SQL 术语中，这类似于:

SELECT questions.id, questions.text, userProfiles.name
FROM userProfiles INNER JOIN questions
ON questions.createdBy = userProfiles.id;

综合起来:

const innerJoin = (xs, ys, sel) =>
    xs.reduce((zs, x) =>
    ys.reduce((zs, y) =>        // cartesian product - all combinations
    zs.concat(sel(x, y) || []), // filter out the rows and columns you want
    zs), []);

const userProfiles = [
    {id: 1, name: "Ashok"},
    {id: 2, name: "Amit"},
    {id: 3, name: "Rajeev"},
];

const questions = [
    {id: 1, text: "text1", createdBy: 2},
    {id: 2, text: "text2", createdBy: 2},
    {id: 3, text: "text3", createdBy: 1},
    {id: 4, text: "text4", createdBy: 2},
    {id: 5, text: "text5", createdBy: 3},
    {id: 6, text: "text6", createdBy: 3},
];

const result = innerJoin(userProfiles, questions,
    ({id: uid, name}, {id, text, createdBy}) =>
        createdBy === uid && {id, text, name});

console.log("Open your browser console to see the output.");

console.table(result);

---

编辑: 然而，这不是最佳解决方案。由于上述解决方案循环遍历了 Cartesian product运行需要 O(m × n) 时间。通过一些修改，我们可以让它在 O(m + n) 时间内运行 - @pebbl found it first :

const equijoin = (xs, ys, primary, foreign, sel) => {
    const ix = xs.reduce((ix, row) => // loop through m items
        ix.set(row[primary], row),    // populate index for primary table
    new Map);                         // create an index for primary table

    return ys.map(row =>              // loop through n items
        sel(ix.get(row[foreign]),     // get corresponding row from primary
        row));                        // select only the columns you need
};

现在您可以按如下方式使用它:

const result = equijoin(userProfiles, questions, "id", "createdBy",
    ({name}, {id, text}) => ({id, text, name}));

综合起来:

const equijoin = (xs, ys, primary, foreign, sel) => {
    const ix = xs.reduce((ix, row) => ix.set(row[primary], row), new Map);
    return ys.map(row => sel(ix.get(row[foreign]), row));
};

const userProfiles = [
    {id: 1, name: "Ashok"},
    {id: 2, name: "Amit"},
    {id: 3, name: "Rajeev"},
];

const questions = [
    {id: 1, text: "text1", createdBy: 2},
    {id: 2, text: "text2", createdBy: 2},
    {id: 3, text: "text3", createdBy: 1},
    {id: 4, text: "text4", createdBy: 2},
    {id: 5, text: "text5", createdBy: 3},
    {id: 6, text: "text6", createdBy: 3},
];

const result = equijoin(userProfiles, questions, "id", "createdBy",
    ({name}, {id, text}) => ({id, text, name}));

console.log("Open your browser console to see the output.");

console.table(result);