0

I have two tables, the match table keeps track of matches and the message_log table keeps track of conversations for a match. Right now I'm getting all the matches by the following query

SELECT * FROM match order by id DESC

But I want to modify it so that it would only return matches that have at least one message in the message_log and at least one message has own_account set to false.

my match table

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
)

my message_log table

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
)
1

You can do this efficiently and concisely with an EXISTS clause.

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

Btw it's really confusing to have match_id, and match_pk in the message_log table. Normally, the convention of _id means you're referencing something else in a different table.

Also, in PostgreSQL we never use character varying(x) unless there is a spec that imposes x you should just be using text.

  • I created this years ago when I was not familiar with Postgres, that's why I'm using charvar, I will change them to text soon. I also agree that my referances are also confusing. I did not know about convention back when I created this. – Arya Nov 1 '17 at 3:20
  • I tested your query, but it returns 0 rows, I know it should return some as I have matches with others replying – Arya Nov 1 '17 at 3:20
  • @Arya if it returns 0 rows, that's all she wrote. There is no problem in the query. Perhaps own_account isn't set to false, and it's null? Shy of that there isn't much room for error on your part either. Create a test case. – Evan Carroll Nov 1 '17 at 3:24
  • 1
    I'm going to look into things. I'll get back to you – Arya Nov 1 '17 at 3:29
  • By the looks of it, I have stopped using match_pk, and using match_id instead. Time to delete the match_pk from my table. I did change the query to this pastebin.com/xEdViAkB and it's returning results. Looks correct to you? – Arya Nov 1 '17 at 3:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.