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?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
