From: | Arup Rakshit <aruprakshit1987(at)outlook(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Exclude posts which was from blacklisted users Sql help |
Date: | 2017-07-22 17:44:48 |
Message-ID: | 7E5B4D83-0457-4BF4-ACAB-C601173325C1@outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
Below is my table structure:
musedb_dev=# \d kudosposts
Table "public.kudosposts"
Column | Type | Modifiers
--------------+-----------------------------+---------------------------------------------------------
id | integer | not null default nextval('kudosposts_id_seq'::regclass)
content | text |
user_id | integer |
created_at | timestamp without time zone | not null
updated_at | timestamp without time zone | not null
pix | character varying |
giphy_id | integer |
destroyed_at | timestamp without time zone |
TABLE "user_posts" CONSTRAINT "fk_rails_3b5b08eb72" FOREIGN KEY (kudospost_id) REFERENCES kudosposts(id)
TABLE "comments" CONSTRAINT "fk_rails_bc8176e8bc" FOREIGN KEY (kudospost_id) REFERENCES kudosposts(id)
musedb_dev=# \d user_posts
Table "public.user_posts"
Column | Type | Modifiers
--------------+-----------------------------+---------------------------------------------------------
id | integer | not null default nextval('user_posts_id_seq'::regclass)
user_id | integer |
kudospost_id | integer |
created_at | timestamp without time zone | not null
updated_at | timestamp without time zone | not null
Foreign-key constraints:
"fk_rails_3b5b08eb72" FOREIGN KEY (kudospost_id) REFERENCES kudosposts(id)
"fk_rails_6c6a346128" FOREIGN KEY (user_id) REFERENCES users(id)
musedb_dev=# \d users
Table "public.users"
Column | Type | Modifiers
------------------------+-----------------------------+----------------------------------------------------
id | integer | not null default nextval('users_id_seq'::regclass)
email | character varying | not null default ''::character varying
black_list_user_ids | integer[] | default '{}'::integer[]
Referenced by:
TABLE "comments" CONSTRAINT "fk_rails_03de2dc08c" FOREIGN KEY (user_id) REFERENCES users(id)
TABLE "settings" CONSTRAINT "fk_rails_5676777bf1" FOREIGN KEY (user_id) REFERENCES users(id)
TABLE "user_posts" CONSTRAINT "fk_rails_6c6a346128" FOREIGN KEY (user_id) REFERENCES users(id)
TABLE "kudosposts" CONSTRAINT "fk_rails_ba6b4c6f54" FOREIGN KEY (user_id) REFERENCES users(id)
TABLE "favorites" CONSTRAINT "fk_rails_d15744e438" FOREIGN KEY (user_id) REFERENCES users(id)
TABLE "user_kudos_milestones" CONSTRAINT "fk_rails_e5a78b2bce" FOREIGN KEY (user_id) REFERENCES users(id)
musedb_dev=#
I am trying list posts whose owner is not in black lists users column of the post receiver. But my sql still selecting the backlisted user posts.
Below is the SQL I tried:
SELECT
"kudosposts".*
FROM
"kudosposts"
INNER JOIN
"user_posts" "user_posts_kudosposts_join"
ON "user_posts_kudosposts_join"."kudospost_id" = "kudosposts"."id"
INNER JOIN
"users"
ON "users"."id" = "user_posts_kudosposts_join"."user_id"
AND "users"."destroyed_at" IS NULL
INNER JOIN
"user_posts"
ON "kudosposts"."id" = "user_posts"."kudospost_id"
WHERE
"kudosposts"."destroyed_at" IS NULL
AND "user_posts"."user_id" = 5
AND
(
kudosposts.user_id != all (users.black_list_user_ids)
)
ORDER BY
"kudosposts"."created_at” DESC
Could you help me to find out where I am wrong?
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Atkins | 2017-07-22 19:11:42 | pg_dump not dropping event trigger |
Previous Message | Igor Korot | 2017-07-22 16:22:32 | Re: Backward compatibility |