Knex/SQL:将一对多合并到一个对象中 [英] Knex/SQL: Merge one to many join in one object

问题描述

我有两个表:"page"和"page_css".我希望页面和page_css之间存在一对多的关系(一个页面包含多个page_css). 查看表格结构:

I got two tables: 'page' and 'page_css'. I want a one to many relationship between page and page_css (one page contains multiple page_css). See table structure:

页表结构:

Page_css表结构:

Page_css table structure:

这是我正在使用的Knex查询:

This is the Knex query I am using:

var page = knex('page')
  .where({ slug: req.params.slug })
  .join('page_css', { 'page_css.page_id': 'page.id' })
  .then(function(page) {
    if (page) {
      return res.json(page);
    }
    return res.send('page not found');
  });

我得到的结果是

但是我想要这样的结果:

But I want the result like this:

我需要运行哪种SQL/Knex查询才能使其像上面的格式一样?

What SQL/Knex query do I need to run to get it like the format above?

推荐答案

Knex不能做魔术，它只是正好"的查询生成器，可以构造适当的SQL查询.

Knex can't do magic, it is "just" query builder that construct proper SQL query.

您将需要根据需要将此平坦结果转换为对象.

You will need to convert this flat result into object like you wish.

const flatData = [{
  id: 1,
  slug: 'test',
  page_id: 1,
  css_file: 'main'
}, {
  id: 1,
  slug: 'test',
  page_id: 1,
  css_file: 'hero'
}];

const resultMap = flatData.reduce((result, row) => {
  result[row.id] = result[row.id] || {
    ...row,
    css_file: []
  };

  result[row.id].css_file.push(row.css_file);

  return result;
}, {});

console.log(Object.values(resultMap));

这篇关于Knex/SQL:将一对多合并到一个对象中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！