| 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: | Whole Thread | Raw Message | 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 |