Reputation: 3882
I am using postgres-node
, but I think this is a problem for anyone with javascript objects that have subarrays they want to save in SQL. I have a javascript objects with a varying amount (any-length) array of features:
{
name: "Ted",
features: ['Red Hair', 'Blue Eyes']
}
so when I have several of them, the javascript formats it like:
[
{
name: "Ted",
features: ['Red Hair', 'Blue Eyes']
},
{
name: "Ann",
features: ['Brown Hair', 'Blue Eyes', 'Big Smile']
}
]
Which is great! But how do I get this back from the database after normalizing? I have normalized this in my database like this:
people
Table
+---+------------+
|id | Name |
+---+------------+
| 1 | Ted |
| 2 | Ann |
+---+------------+
features
table
+---+--------------+
|id | feature_name |
+---+--------------+
| 1 | Red Hair |
| 2 | Blue Eyes |
| 3 | Brown Hair |
| 4 | Big Smile |
+---+--------------+
and people_features
junction table
+---+-----------+-------------+
|id | person_id | feature_id |
+---+-----------+-------------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 2 |
| 4 | 2 | 3 |
| 5 | 2 | 4 |
+---+-----------+-------------+
If I do a join like this:
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;
I get one row for every single person. Which isn't what I want.
What I get:
[
{
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'
}
]
What I want:
[
{
name: "Ted",
features: ['Red Hair', 'Blue Eyes']
},
{
name: "Ann",
features: ['Brown Hair', 'Blue Eyes', 'Big Smile']
}
]
This seems awful! Now I need to loop through these and combine the identical people into a single person object. My other option seems to be making a request for the people
SELECT id, name
FROM people;
Which would return:
[
{
id: 1
name: "Ted"
},
{
id: 2
name: "Ann"
}
]
And then I need to loop through and make a separate SQL query for every single person?
For each person:
SELECT feature_name
FROM features
JOIN people_features ON features.id=people_features.feature_id
WHERE people_features.person_id = $1
($1 is the person's id that I am looping through)
And then I would get back (for Ted):
[
{ feature_name: 'Red Hair' },
{ feature_name: 'Blue Eyes' }
]
Then I need to remove these from their objects (to just get the string) and then add them to the object.
Is one of these the best way to do it? I feel like they are both really inefficient.
Upvotes: 3
Views: 309
Reputation: 3882
Vao Tsun's answer is perfect. For those interested, here is the minimal version of what I ended up using in my node code to get it working with 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
}
);
}
});
Upvotes: 1
Reputation: 51659
This should do it:
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;
Upvotes: 1