get la tabla JOIN como una matriz de resultados con PostgreSQL / NodeJS

Estoy creando una aplicación donde los usuarios pueden crear preguntas, y otros pueden votar / rechazarlos.

Lo siguiente es una parte de mi esquema sql:

CREATE TABLE "questions" ( id SERIAL, content VARCHAR(511) NOT NULL, created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), CONSTRAINT pk_question PRIMARY KEY (id) ); CREATE TABLE "votes" ( id SERIAL, value INT, question_id INT NOT NULL, CONSTRAINT pk_vote PRIMARY KEY (id), CONSTRAINT fk_question_votes FOREIGN KEY (question_id) REFERENCES questions (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ); 

Lo que me gustaría es que Postgres me haga cada pregunta con una serie de votos, así:

 [{ // a question id: 1, content: 'huh?', votes: [{ // a vote id: 1, value: 1 }, { // another vote id: 2, value: -1 }] }, { /*another question with votes*/ }] 

Miré funciones agregadas (como array_agg ()) pero me dio solo los valores. A JOIN me dio una pregunta junto con un voto, y me obligaría a hacer operaciones del lado del server, que preferiría no hacerlo.

¿Hay alguna forma de hacer eso? ¿Mi razonamiento con respecto a lo que quiero get es incorrecto?

Gracias por tu time.

Esto es fácil de hacer con pg-promise :

 function buildTree(t) { return t.map('SELECT * FROM questions', [], q => { return t.any('SELECT id, value FROM votes WHERE question_id = $1', q.id) .then(votes => { q.votes = votes; return q; }); }).then(t.batch); // settles the array of promises generated } db.task(buildTree) .then(data => { console.log(data); // your data tree }) .catch(error => { console.log(error); }); 

API: map , cualquier , tarea , lote


Preguntas relacionadas:

  • Obtener un tree de padres + hijos con la promise de pg
  • Tarea condicional con pg-promise

Y si quiere usar solo una consulta, entonces usando la syntax de PostgreSQL 9.4 y posterior puede hacer lo siguiente:

 SELECT json_build_object('id', q.id, 'content', q.content, 'votes', (SELECT json_agg(json_build_object('id', v.id, 'value', v.value)) FROM votes v WHERE q.id = v.question_id)) FROM questions q 

Y luego su ejemplo de promise de pg sería:

 var query = `SELECT json_build_object('id', q.id, 'content', q.content, 'votes', (SELECT json_agg(json_build_object('id', v.id, 'value', v.value)) FROM votes v WHERE q.id = v.question_id)) json FROM questions q`; db.map(query, [], a => a.json) .then(data => { console.log(data); // your data tree }) .catch(error => { console.log(error); }); 

Conclusión

La elección entre los dos enfoques presentados anteriormente debe basarse en los requisitos de performance de su aplicación:

  • El enfoque de una sola consulta es más rápido, pero es algo difícil de leer o ampliar, siendo bastante detallado
  • El enfoque de consultas múltiples es más fácil de entender y extender, pero no es excelente para el performance, debido al número dynamic de consultas ejecutadas.