Exclude posts which was from blacklisted users Sql help

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?

Browse pgsql-general by date

  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