从新闻提要获取评论的查询

bkkx9g8r  于 2021-06-18  发布在  Mysql
关注(0)|答案(3)|浏览(226)

当一个人发布新闻源时，以及当其他用户成为朋友时，必须在检查他/她是否是朋友的基础上选择他/她和他/她的朋友的新闻源，并且必须选择对新闻源的评论
这是我的下表结构

下面的查询成功地从这个人以及这个人的朋友那里获取了新闻提要，并提供了一个有效的用户名和用户的照片

$data = $this->db->query("
SELECT DISTINCT(ft.ID) as ID, ft.userid, ft.content, ft.timestamp, 
                ft.likes, ft.comments, u.username, u.avatar 
    FROM feed_item ft, users u 
    WHERE ft.userid = u.ID AND ft.userid 
    IN 
    (SELECT u.ID 
     FROM users u 
     WHERE 
        u.ID IN (SELECT uf.friendid FROM user_friends uf WHERE uf.status = '2' AND uf.userid = '".$this->user->info->ID."') 
     OR u.ID IN (SELECT uf.userid FROM user_friends uf WHERE uf.status = '2' AND uf.friendid = '".$this->user->info->ID."')
     OR u.ID = '".$this->user->info->ID."'
    ) 
ORDER BY ft.ID DESC")->result_array();

但是有一次，我修改了查询，只从这个人的朋友那里检索所有的评论。
这导致获取用户名和用户照片的值为空

$data = $this->db->query("
SELECT DISTINCT ft.ID as ID, ft.userid, ft.content, ft.timestamp, 
       ft.likes, ft.comments, ftc.comment, u.username, u.avatar 
FROM  feed_item_comment ftc
LEFT JOIN feed_item ft 
  ON ftc.postid = ft.ID 
 AND ftc.userid != '".$this->user->info->ID."' AND ftc.userid = ft.userid
LEFT JOIN user_friends uf 
  ON uf.friendid = ftc.userid 
LEFT JOIN users u 
  ON u.ID = uf.friendid 
 AND ft.userid  IN 
(SELECT u.ID 
 FROM users u 
 WHERE 
    u.ID IN (SELECT uf.friendid FROM user_friends uf WHERE uf.status = '2' AND uf.userid = '".$this->user->info->ID."') 
 OR u.ID IN (SELECT uf.userid FROM user_friends uf WHERE uf.status = '2' AND uf.friendid = '".$this->user->info->ID."')
 OR u.ID = '".$this->user->info->ID."'
) 
ORDER BY ft.ID DESC")->result_array();

我应该如何编写与第一个查询相关的查询，以便将注解从嵌套的注解中获取到新闻提要，并使用来自此人以及具有有效用户名和用户照片的好友的帖子发布到新闻提要？

更新

-- phpMyAdmin SQL Dump
-- version 4.8.3
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Oct 09, 2018 at 04:18 PM
-- Server version: 10.1.36-MariaDB
-- PHP Version: 7.2.10

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `gamersapi`
--

-- --------------------------------------------------------

--
-- Table structure for table `feed_item_comment`
--

CREATE TABLE `feed_item_comment` (
  `ID` int(11) NOT NULL,
  `postid` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  `comment` varchar(3000) NOT NULL,
  `timestamp` int(11) NOT NULL,
  `likes` int(11) NOT NULL,
  `commentid` int(11) NOT NULL,
  `replies` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `feed_item_comment`
--

INSERT INTO `feed_item_comment` (`ID`, `postid`, `userid`, `comment`, `timestamp`, `likes`, `commentid`, `replies`) VALUES
(1, 184, 1, 'comment', 1539080007, 0, 0, 0),
(2, 186, 14, 'VBVBVB', 1539084437, 0, 0, 0),
(3, 186, 14, 'VVV', 1539084448, 0, 0, 0),
(4, 187, 4, 'zzz', 1539084875, 0, 0, 0);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `feed_item_comment`
--
ALTER TABLE `feed_item_comment`
  ADD PRIMARY KEY (`ID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `feed_item_comment`
--
ALTER TABLE `feed_item_comment`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

-- phpMyAdmin SQL Dump
-- version 4.8.3
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Oct 09, 2018 at 04:18 PM
-- Server version: 10.1.36-MariaDB
-- PHP Version: 7.2.10

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `gamersapi`
--

-- --------------------------------------------------------

--
-- Table structure for table `feed_item`
--

CREATE TABLE `feed_item` (
  `ID` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  `content` text NOT NULL,
  `timestamp` time NOT NULL,
  `imageid` int(11) NOT NULL,
  `likes` int(11) NOT NULL,
  `comments` int(11) NOT NULL,
  `user_flag` int(11) NOT NULL,
  `likes_data` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `feed_item`
--

INSERT INTO `feed_item` (`ID`, `userid`, `content`, `timestamp`, `imageid`, `likes`, `comments`, `user_flag`, `likes_data`) VALUES
(1, 1, 'How are you', '00:00:00', 0, 0, 0, 0, 'like'),
(2, 1, 'How are you doing', '00:00:00', 0, 0, 0, 0, 'like'),
(3, 1, 'This is my test', '00:00:00', 0, 0, 0, 0, 'like'),
(4, 1, 'Hello', '838:59:59', 0, 0, 0, 0, 'like'),
(5, 1, 'hello', '00:00:00', 0, 0, 0, 0, 'like'),
(6, 1, 'Hello hi', '00:00:00', 0, 0, 0, 0, 'like'),
(7, 1, 'gmail', '00:00:00', 0, 0, 0, 0, 'like'),

--
-- Indexes for dumped tables
--

--
-- Indexes for table `feed_item`
--
ALTER TABLE `feed_item`
  ADD PRIMARY KEY (`ID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `feed_item`
--
ALTER TABLE `feed_item`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=188;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

-- phpMyAdmin SQL Dump
-- version 4.8.3
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Oct 09, 2018 at 04:20 PM
-- Server version: 10.1.36-MariaDB
-- PHP Version: 7.2.10

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `gamersapi`
--

-- --------------------------------------------------------

--
-- Table structure for table `user_friends`
--

CREATE TABLE `user_friends` (
  `ID` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  `friendid` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  `timestamp` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `user_friends`
--

INSERT INTO `user_friends` (`ID`, `userid`, `friendid`, `status`, `timestamp`) VALUES
(1, 8, 4, 2, 1538369252),
(2, 1, 2, 2, 1538454842),
(3, 7, 1, 2, 1538455395),
(4, 7, 2, 2, 1538455487),
(5, 11, 2, 3, 1538455512),
(6, 6, 2, 2, 1538455567),
(7, 2, 5, 2, 1538456136),
(8, 1, 6, 1, 1538491568),
(9, 12, 1, 2, 1538499199),
(12, 1, 7, 1, 1538565860),
(13, 14, 1, 2, 1538800794);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `user_friends`
--
ALTER TABLE `user_friends`
  ADD PRIMARY KEY (`ID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `user_friends`
--
ALTER TABLE `user_friends`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `user_friends`
--
ALTER TABLE `user_friends`
  ADD CONSTRAINT `user_friends_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `users` (`ID`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
sqlmysqlJoinsubquerycorrelated-subquery

3条答案

按热度按时间
siv3szwd

siv3szwd1#

考虑更换 ON 条款条件， AND ft.userid IN (SELECT u.ID ... ，对于 WHERE 子句，因为此表达式是在非提要项上设置的 JOIN ，特别是用户 JOIN :

SELECT DISTINCT ft.ID as ID, ft.userid, ft.content, ft.timestamp, 
       ft.likes, ft.comments, ftc.comment, u.username, u.avatar 
FROM feed_item_comment ftc
LEFT JOIN feed_item ft 
  ON ftc.postid = ft.ID 
 AND ftc.userid = ft.userid
 AND ftc.userid != '".$this->user->info->ID."'
LEFT JOIN user_friends uf 
  ON uf.friendid = ftc.userid 
LEFT JOIN users u 
  ON u.ID = uf.friendid 
WHERE ft.userid IN               -- ONLY CHANGE
  (SELECT u.ID 
   FROM users u 
    WHERE u.ID IN (SELECT uf.friendid FROM user_friends uf 
                   WHERE uf.status = '2' AND uf.userid = '".$this->user->info->ID."') 
       OR u.ID IN (SELECT uf.userid FROM user_friends uf 
                   WHERE uf.status = '2' AND uf.friendid = '".$this->user->info->ID."')
       OR u.ID = '".$this->user->info->ID."'
  )     
ORDER BY ft.ID DESC

看到这条线了吗 WHERE 与 ON 带条件的子句 LEFT JOIN .

赞(0）分享  回复(0）举报  2021-06-18
nhjlsmyf

nhjlsmyf2#

下面的查询用于获取所有新闻提要项

SELECT DISTINCT
  ft.ID AS ID,
  ft.userid,
  ft.content,
  ft.timestamp,
  ft.likes,
  ft.comments,
  u.username,
  u.avatar,
  ft.friend_id,
  ft.friend_username
FROM
  feed_item ft
  LEFT JOIN users u
    ON ft.userid = u.ID
WHERE ft.userid = u.ID
  AND ft.userid IN
  (SELECT
    u1.ID
  FROM
    users u1
  WHERE u1.ID IN
    (SELECT
      uf.friendid
    FROM
      user_friends uf
    WHERE uf.status = '2'
      AND uf.userid = '".$this->user->info->ID."')
    OR u1.ID IN
    (SELECT
      uf2.userid
    FROM
      user_friends uf2
    WHERE uf2.status = '2'
      AND uf2.friendid = '".$this->user->info->ID."')
    AND u1.ID != '".$this->user->info->ID."')
ORDER BY ft.ID DESC

当从脚本的服务器端（即php）迭代上面执行的sql的值时，我们将得到与新闻提要的post id相关的所有注解

SELECT
  u2.username,
  u2.avatar,
  ftc.ID,
  ftc.postid,
  ftc.userid,
  ftc.comment,
  ftc.timestamp AS cmtTime,
  ftc.likes,
  ftc.commentid,
  ftc.replies
FROM
  feed_item_comment ftc
  JOIN users u2
    ON (u2.ID = ftc.userid)
WHERE ftc.postid = '".$ROW[' ID ']."'
ORDER BY ftc.ID

这个逻辑对我来说很有用，如果没有必要的话，我已经包含了更多的列值，忽略保留其余的。执行单个查询将非常麻烦

赞(0）分享  回复(0）举报  2021-06-18
qmelpv7a

qmelpv7a3#

这就是我所拥有的。但因为你渴望的结果并不明确，我不想花太多时间试图解决一些可能不是你所需要的。
sql演示

-- get friends of userid = 1

SELECT CASE WHEN userid = 1 THEN friendid
            WHEN friendid = 1 THEN userid
            ELSE id 
       END as id
FROM `user_friends` 
WHERE ID = 1 
   OR ( 1 IN ( `userid`, `friendid`) AND `status` = 2);

-- get all the messages from 1 and his friends

SELECT *
FROM feed_item
WHERE userid IN (
                    SELECT CASE WHEN userid = 1 THEN friendid
                                WHEN friendid = 1 THEN userid
                                ELSE id 
                           END as id
                    FROM `user_friends` 
                    WHERE ID = 1 
                       OR ( 1 IN ( `userid`, `friendid`) AND `status` = 2)
                );
赞(0）分享  回复(0）举报  2021-06-18
我来回答

相关问题

    查看更多
    微信公众号

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com