funciones - manual de postgresql 10 en español pdf

obtener la tabla JOIN como matriz de resultados con PostgreSQL/NodeJS

Question

Estoy creando una aplicación donde los usuarios pueden crear preguntas, y otros pueden votarlas a favor o en contra.

Lo siguiente es 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 tener es Postgres dándome cada pregunta con una variedad 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é las funciones agregadas (como array_agg ()) pero solo me dio los valores. A JOIN me dio una pregunta unida con un voto y me obligaría a realizar operaciones del lado del servidor, lo que preferiría no hacer.

¿Hay alguna forma de hacer eso? ¿Mi razonamiento sobre lo que quiero obtener es incorrecto?

Gracias por tu tiempo.

Answer 1

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 generated promises
}

db.task(buildTree)
    .then(data => {
        console.log(data); // your data tree
    })
    .catch(error => {
        console.log(error);
    });

API: map , any , task , batch

Preguntas relacionadas:

• Obtenga un árbol de padres e hijos con pg-promise
• Tarea condicional con pg-promise

Y si desea utilizar solo una consulta, luego, utilizando PostgreSQL 9.4 y una sintaxis 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 pg-promise sería:

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

Y definitivamente querrá mantener consultas tan complejas en archivos SQL externos. Ver archivos de consulta .

Conclusión

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

• El enfoque de consulta única es más rápido, pero es algo difícil de leer o ampliar, ya que es bastante detallado
• El enfoque de consultas múltiples es más fácil de entender y ampliar, pero no es excelente para el rendimiento, debido al número dinámico de consultas ejecutadas.

ACTUALIZAR

La siguiente respuesta relacionada ofrece más opciones, al concatenar consultas secundarias, lo que proporcionará un rendimiento mucho mejor: Combine consultas de bucle anidado con el resultado primario pg-promise .

Answer 2

Por favor, piense de manera simple, puede que tenga razón, uso knex js

 let allpost = knex
        .select([
            ''questions.id'',
            ''question.content'',
            knex.raw(''json_agg(v.*) as votes'')
        ])
        .from(''questions'')
        .leftJoin(''votes as v'', ''questions.id'', ''v.question_id'')
        .groupBy(''questions.id'');

Answer 3

sql-toolkit hace exactamente esto. Es una biblioteca de nodos creada para pg-promise que le permite escribir SQL nativo regular y recibir objetos comerciales puros correctamente estructurados (anidados), sin tener que dividir la consulta o reescribirla con json_build_object .

Por ejemplo:

class Article extends BaseDAO {
  getBySlug(slug) {
    const query = `
      SELECT
        ${Article.getSQLSelectClause()},
        ${Person.getSQLSelectClause()},
        ${ArticleTag.getSQLSelectClause()},
        ${Tag.getSQLSelectClause()}
      FROM article
      JOIN person
        ON article.author_id = person.id
      LEFT JOIN article_tags
        ON article.id = article_tags.article_id
      LEFT JOIN tag
        ON article_tags.tag_id = tag.id
      WHERE article.slug = $(slug);
  `;
  return this.one(query, { slug });
  // OUTPUT: Article {person: Person, tags: Tags[Tag, Tag, Tag]}
}

La cláusula select utiliza los métodos de "getSQLSelectClause" del objeto comercial para ahorrar tedio al escribir las columnas, así como para garantizar que no haya colisiones de nombres (no ocurre nada mágico, y podría escribirse en su lugar).

this.one es una llamada a la clase base DAO de sql-toolkit . Es responsable de estructurar los registros de resultados planos en una bonita estructura anidada.

(Observe también que es "uno" que coincide con nuestro modelo mental para el SQL. Los métodos DAO para uno, uno u otro, muchos y cualquiera aseguran su recuento contra el número de objetos comerciales de nivel superior generados, no el número de filas del sql ¡la expresión vuelve!)

Consulte el sql-toolkit para obtener detalles sobre cómo configurarlo además de pg-promise . Es estrictamente una mejora, y no busca abstraer pg-promise (aún configura pg-promise y puede usarlo directamente). (Descargo de responsabilidad, soy el autor de sql-toolkit ).