Как сохранить и вернуть объект JavaScript с подмассивом в нормализованном SQL

Я использую postgres-node, но я думаю, что это проблема для всех, у кого есть объекты javascript, у которых есть подмассивы, которые они хотят сохранить в SQL. У меня есть объекты javascript с различным количеством (любой длины) массива функций:

{ 
  name: "Ted",
  features: ['Red Hair', 'Blue Eyes']
}

поэтому, когда у меня их несколько, javascript форматирует его так:

[
  { 
    name: "Ted",
    features: ['Red Hair', 'Blue Eyes']
  },
  { 
    name: "Ann",
    features: ['Brown Hair', 'Blue Eyes', 'Big Smile']
  }
]

Это здорово! Но как мне вернуть это из базы данных после нормализации? Я нормализовал это в своей базе данных следующим образом:

people Таблица

+---+------------+
|id | Name       |
+---+------------+
| 1 | Ted        |
| 2 | Ann        |
+---+------------+

features таблица

+---+--------------+
|id | feature_name |
+---+--------------+
| 1 | Red Hair     |
| 2 | Blue Eyes    |
| 3 | Brown Hair   |
| 4 | Big Smile    |
+---+--------------+

и people_features соединительная таблица

+---+-----------+-------------+
|id | person_id | feature_id  |
+---+-----------+-------------+
| 1 | 1         | 1           |
| 2 | 1         | 2           |
| 3 | 2         | 2           |
| 4 | 2         | 3           |
| 5 | 2         | 4           |
+---+-----------+-------------+

Если я сделаю соединение следующим образом:

SELECT name, feature_name
FROM people
JOIN people_features ON people_features.person_id=people.id
JOIN features ON people_features.feature_id=features.id;

Я получаю одну строку для каждого человека. Это не то, чего я хочу.

Что я получаю:

[
  { 
    name: "Ted",
    feature_name: 'Red Hair'
  },
  { 
    name: "Ted",
    feature_name: 'Blue Eyes'
  },
  { 
    name: "Ann",
    feature_name: 'Blue Eyes'
  },
  { 
    name: "Ann",
    feature_name: 'Brown Hair'
  },
  { 
    name: "Ann",
    feature_name: 'Big Smile'
  }
]

Что я хочу:

[
  { 
    name: "Ted",
    features: ['Red Hair', 'Blue Eyes']
  },
  { 
    name: "Ann",
    features: ['Brown Hair', 'Blue Eyes', 'Big Smile']
  }
]

Это кажется ужасным! Теперь мне нужно перебрать их и объединить одинаковых людей в один объект-человек. Мой другой вариант, кажется, делает запрос для людей

SELECT id, name
FROM people;

Что вернет:

[
  { 
    id: 1
    name: "Ted"
  },
  { 
    id: 2
    name: "Ann"
  }
]

И тогда мне нужно выполнить цикл и сделать отдельный SQL-запрос для каждого человека?

Для каждого человека:

SELECT feature_name
FROM features
JOIN people_features ON features.id=people_features.feature_id
WHERE people_features.person_id = $1

($1 — это идентификатор человека, через который я просматриваю)

И тогда я возвращался (для Теда):

[
  { feature_name: 'Red Hair' },
  { feature_name: 'Blue Eyes' }
]

Затем мне нужно удалить их из своих объектов (чтобы просто получить строку), а затем добавить их к объекту.

Является ли один из них лучшим способом сделать это? Я чувствую, что они оба действительно неэффективны.


postgresql javascript sql node-postgres
person Luke Schlangen    schedule 17.04.2017    source источник
comment
Вы ищете что-то подобное? - stackoverflow.com/questions/39805736/   -  person vitaly-t    schedule 18.04.2017
comment
Ответ от Вао Цун был именно тем, что я искал. Спасибо, что поделились этим!   -  person Luke Schlangen    schedule 18.04.2017

Ответы (2)


arrow_upward
1
arrow_downward

Это должно сделать это:

SELECT name, array_agg(feature_name)
FROM people
JOIN people_features ON people_features.person_id=people.id
JOIN features ON people_features.feature_id=features.id
GROUP BY people.id;
person Vao Tsun    schedule 17.04.2017

arrow_upward
1
arrow_downward

Ответ Вао Цуна идеален. Для тех, кому интересно, вот минимальная версия того, что я использовал в своем коде узла, чтобы заставить его работать с node-postgres:

var pg = require('pg');
var config = {
    user: process.env.PG_USER || null, //env var: PGUSER
    password: process.env.DATABASE_SECRET || null, //env var: PGPASSWORD
    host: process.env.DATABASE_SERVER || 'localhost', // Server hosting the postgres database
    port: process.env.DATABASE_PORT || 5432, //env var: PGPORT    
    database: process.env.DATABASE_NAME || 'lukeschlangen', //env var: PGDATABASE
    max: 10, // max number of clients in the pool
    idleTimeoutMillis: 30000, // how long a client is allowed to remain idle before being closed
};

var pool = new pg.Pool(config)

pool.connect(function (err, client, done) {
    if (err) {
        console.log('There was an error', err);
    } else {
        client.query(
            'SELECT name, array_agg(feature_name) ' +
            'FROM people ' +
            'JOIN people_features ON people_features.person_id=people.id ' +
            'JOIN features ON people_features.feature_id=features.id ' +
            'GROUP BY people.id;',
            function (err, results) {
                done();
                console.log(results.rows); // This was exactly the array I wanted
            }
        );

    }
});
person Luke Schlangen    schedule 17.04.2017