obter a tabela JOIN como matriz de resultados com o PostgreSQL / NodeJS

Estou criando um aplicativo em que os usuários podem criar perguntas e outros podem fazer um upvote / downvote para eles.

O seguinte é uma parte do meu 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 ); 

O que eu gostaria de ter é o Postgres me dando cada pergunta com uma série de votos, assim:

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

Eu olhei para funções agregadas (como array_agg ()), mas me deu apenas os valores. Um JOIN me fez uma pergunta associada a uma votação e me forçaria a fazer operações do lado do servidor, o que eu preferiria não fazer.

Existe alguma maneira de fazer isso? Meu raciocínio é sobre o que eu quero obter errado?

Obrigado pelo seu tempo.

Isso é fácil de fazer com o 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 generated promises } db.task(buildTree) .then(data => { console.log(data); // your data tree }) .catch(error => { console.log(error); }); 

API: map , any , task , batch


Perguntas relacionadas:

  • Obtenha uma tree pais + filhos com pg-promise
  • Tarefa condicional com pg-promise

E se você quiser usar apenas uma única consulta, usando a syntax do PostgreSQL 9.4 e posterior, você pode fazer o seguinte:

 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 

E então o seu exemplo de pg-promise seria:

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

E você definitivamente vai querer manter essas consultas complexas em arquivos SQL externos. Veja Arquivos de Consulta .

Conclusão

A escolha entre as duas abordagens apresentadas acima deve ser baseada nos requisitos de desempenho de sua aplicação:

  • A abordagem de consulta única é mais rápida, mas é um pouco difícil de ler ou estender, sendo bastante detalhada
  • A abordagem de consulta múltipla é mais fácil de entender e estender, mas não é excelente para desempenho, devido ao número dynamic de consultas executadas.

ATUALIZAR

A resposta relacionada a seguir oferece mais opções, concatenando consultas filho, o que proporcionará um desempenho muito melhor: Combine consultas de loop nested ao resultado pai pg-promise .