Knex/SQL: Merge one to many join in one object

Question

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 table structure:

Page_css table structure:

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');
  });

This the result I'm getting:

But I want the result like this:

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

Answer 1

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));