mysql - JSON_CONTAINS 跨表

Question

已关闭。此问题需要 debugging details 。目前不接受答案。

---

编辑问题以包含 desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem 。这将有助于其他人回答问题。

已关闭 6 年前。

Improve this question

我正在为我的学校开发一种社交网络。

数据库中有两个表有两个表:

• users ，其中包含 (varchar) username和 (json) friends —其中包含用户的好友用户名列表 —并且
• posts ，其中包含 (varchar) publisher field 。

如何编写一个查询来返回发布者与给定用户是好友的所有帖子？

帖子示例:

| id  | publisher  | originalPublisher | postdate   | content                                                                       | likes |
| 113 | pvaqueiroz | NULL              | 2017-03-13 | {"contentType":"text","content":"DAB \\o\\","attatchments":[]} |     0 |

用户示例:

| id | username   | password                         | email                | fullname      | likes                                         | friends                                               | profpic                                                 |
|  1 | pvaqueiroz | 827ccb0eea8a706c4c34a16891f84e7b | pvaqueiroz@gmail.com | Paulo Queiroz | [31, 30, 0, 63, 68, 85, 89, 91, 92, 109, 114] | ["hacker", "girassol_l", "Dment", "Leel", "Mr Dibre"] | /carlos/posts_res/d11109ac342482457f87611483d661a6.jpeg |

Answer 1

这是正确的解决方案。

创建语句(跳过字段，因为不需要解释)

CREATE TABLE `users` (
  `id` INT  UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `email` VARCHAR(45) NULL,
  `password` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `posts` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `post` VARCHAR(45) NULL,
  `user_id` INT UNSIGNED NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_posts_1_idx` (`user_id` ASC),
  CONSTRAINT `fk_posts_1`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE `friends` (
  `user_id` INT UNSIGNED NULL,
  `friend_id` INT UNSIGNED NULL,
  UNIQUE INDEX `user_friend_idx` (`user_id` ASC, `friend_id` ASC),
  INDEX `fk_friends_f_users_idx` (`friend_id` ASC),
  CONSTRAINT `fk_friends_u_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_friends_f_users`
    FOREIGN KEY (`friend_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

每次向用户添加一个fried时，您只需在friends表中记录一对ID

查询将是:

-- Selecting user friends
SELECT * FROM friends f 
JOIN users u ON u.id = f.friend_id
WHERE f.user_id = 1 -- Id of user you need friends for

-- Selecting friends posts
SELECT * FROM friends f 
JOIN posts p ON p.user_id = f.friend_id
WHERE f.user_id = 1 -- Id of user you need friends' posts for