Início / dba / Perguntas / 189817
Accepted
Arya
Asked: 2017-11-01 19:06:40 +0800 CST

Retornar correspondências que tenham pelo menos uma mensagem

  • 772

Eu tenho duas mesas, a matchmesa acompanha as partidas e a message_logmesa acompanha as conversas para uma partida. Agora estou recebendo todas as correspondências pela seguinte consulta

SELECT * FROM match order by id DESC

Mas eu quero modificá-lo para que ele retorne apenas correspondências que tenham pelo menos uma mensagem no message_loge pelo menos uma mensagem own_accountdefinida como false.

minha mesa de jogos

CREATE TABLE public.match
(
    id bigint NOT NULL DEFAULT nextval('match_id_seq'::regclass),
    match_id character varying(500) COLLATE pg_catalog."default",
    name character varying(30) COLLATE pg_catalog."default",
    birthdate timestamp with time zone,
    bio text COLLATE pg_catalog."default",
    my_id character varying(500) COLLATE pg_catalog."default",
    username character varying(500) COLLATE pg_catalog."default",
    insert_time timestamp with time zone DEFAULT now(),
    account_id bigint,
    CONSTRAINT match_pkey PRIMARY KEY (id),
    CONSTRAINT match_match_id_key UNIQUE (match_id),
    CONSTRAINT matcch_account_fk FOREIGN KEY (account_id)
        REFERENCES public.account (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
)

minha tabela message_log

CREATE TABLE public.message_log
(
    id bigint NOT NULL DEFAULT nextval('message_log_id_seq'::regclass),
    message text COLLATE pg_catalog."default",
    from_id text COLLATE pg_catalog."default",
    to_id text COLLATE pg_catalog."default",
    match_id text COLLATE pg_catalog."default",
    unix_timestamp bigint,
    own_account boolean,
    reply_batch boolean DEFAULT false,
    insert_time timestamp with time zone DEFAULT now(),
    account_id bigint,
    match_pk bigint,
    CONSTRAINT message_log_pkey PRIMARY KEY (id),
    CONSTRAINT message_log_message_from_id_to_id_match_id_unix_timestamp_key UNIQUE (message, from_id, to_id, match_id, unix_timestamp),
    CONSTRAINT message_log_account_fk FOREIGN KEY (account_id)
        REFERENCES public.account (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT message_log_match_id_fk FOREIGN KEY (match_pk)
        REFERENCES public.match (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
)
postgresql

1 respostas

  1. Best Answer

    Você pode fazer isso de forma eficiente e concisa com uma EXISTScláusula.

    SELECT *
FROM match
WHERE EXISTS (
  SELECT 1
  FROM message_log
  WHERE match_pk = match.id
  AND own_account IS FALSE
)
ORDER BY id DESC;

    Aliás, é realmente confuso ter match_id, e match_pkna message_logtabela. Normalmente, a convenção de _idsignifica que você está referenciando outra coisa em uma tabela diferente.

    Além disso, no PostgreSQL nunca usamos character varying(x)a menos que haja uma especificação que imponha que xvocê deve usar apenas text.

    • 1

relate perguntas