Início / dba / Perguntas / 332578
Accepted
user1632812
Asked: 2023-10-27 15:58:04 +0800 CST

contando registros relacionados

  • 772

Tenho 2 modelos relacionados, model_A e model_B

Há uma relação 1:n entre eles

Significa que um registro no model_A pode ter vários registros relacionados no model_B

Gostaria que model_A tivesse uma coluna gerada (e armazenada)

Essa coluna gerada deve ser booleana

Deve ser definido como verdadeiro se houver 1 ou mais registros relacionados no modelo_B

Deve ser falso se houver 0 registros relacionados no modelo_B

Como escrevo a expressão para gerar esses valores booleanos?

postgresql

1 respostas

  1. Best Answer

    Você precisa de um gatilho model_bque atualize a coluna redundante em model_a.

    Não use boolean, porque isso força você a contar as linhas filhas para cada modificação de dados. Essa contagem não apenas exigiria algum esforço, mas também introduziria uma condição de corrida: se duas transações simultâneas excluíssem uma linha, nenhuma poderia ver os efeitos da outra, e você poderia acabar com um resultado errado.

    Use uma coluna integerou bigint:

    CREATE TABLE model_a(
   a_id bigint PRIMARY KEY,
   b_count integer DEFAULT 0 NOT NULL
);

CREATE TABLE model_b(
   b_id bigint PRIMARY KEY,
   a_id bigint REFERENCES model_a
);

CREATE FUNCTION laurenz.maintain_count() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
      UPDATE model_a
      SET b_count = b_count + 1
      WHERE a_id = NEW.a_id;
   END IF;

   IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
      UPDATE model_a
      SET b_count = b_count - 1
      WHERE a_id = OLD.a_id;
   END IF;

   RETURN coalesce(NEW, OLD);
END;$$;

CREATE TRIGGER maintain_count
   AFTER INSERT OR UPDATE OF a_id OR DELETE ON model_b
   FOR EACH ROW EXECUTE FUNCTION maintain_count();

    Demonstração:

    INSERT INTO model_a (a_id) VALUES (1), (2);
INSERT 0 2

INSERT INTO model_b (b_id, a_id) VALUES (101, 1), (102, 1), (103, 2);
INSERT 0 3

TABLE model_a;

 a_id │ b_count 
══════╪═════════
    1 │       2
    2 │       1
(2 rows)

UPDATE model_b SET a_id = 2 WHERE b_id = 102;
UPDATE 1

TABLE model_a;

 a_id │ b_count 
══════╪═════════
    2 │       2
    1 │       1
(2 rows)

DELETE FROM model_b WHERE b_id = 103;
DELETE 1

TABLE model_a;

 a_id │ b_count 
══════╪═════════
    1 │       1
    2 │       1
(2 rows)
    • 0

relate perguntas