Query to fetch comments from the news feed

Question

Once when a person posts the news feed and also when other users become a friend, both the person and his/her friend's news feed must be selected based on the checking whether he/she is a friend or not and the comments to the news feed must be selected

Here is my below table structure

The below query succeeded in fetching news feed both from the person as well as the person's friend with a valid username and user's photo

$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();

But once when I modified the query to retrieve all the comments only from the person's friend.

This resulted in getting the username and user's photo with null value

$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();

How am I suppose to write the query with respect to the first query in getting the comments to the news feed from the the nested comments to the news feed with posts that were posted both from the person as well as from person's friend with a valid username and user's photo?

Update

-- 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 */;

`DISTINCT` is not a function. It's a part of `SELECT DISTINCT` and applies to the whole selected rows. Skip those redundant parentheses and simply write `SELECT DISTINCT ft.ID as ID, ft.userid...` to make things clearer. — jarlh, Oct 09 '18 at 13:21
'IN/NOT IN' is often very expensive. With an 'OR' it will be even more. — Daniel E., Oct 09 '18 at 13:31
What is the requirement? Having a PersonA get all message from PersonA plus all messages from friends of PersonA? Can you post your table DDL and some sample data and expected output so we can understand the problem better? — Juan Carlos Oropeza, Oct 09 '18 at 13:59
I have just exported three tables you need to concentrate on those three tables as of now still it's in a development version don't try to analyze the data. I will get back to you after 12 hours @Juan Carlos Oropeza — Nɪsʜᴀɴᴛʜ ॐ, Oct 09 '18 at 14:25
I need to know what result you expect with that data to have an idea what you need — Juan Carlos Oropeza, Oct 09 '18 at 14:44
Yeah! I apologize that the question that was asked was not clearly framed. Since I needed to retrieve the results for the nested comments to the news feed with posts that were posted both from the person as well as from person's friend with a valid username and user's photo! @JuanCarlosOropeza — Nɪsʜᴀɴᴛʜ ॐ, Oct 09 '18 at 16:06
Referring to the first query I used to get only all the news feed posts from both from the person and from the person's friend with username & profile photo. But I need to retrieve all the comments to the news feed posts only iff the person's friend commented out! @Juan Carlos Oropeza — Nɪsʜᴀɴᴛʜ ॐ, Oct 09 '18 at 16:16
Again need an example of your desire result to be sure I try to solve the right problem. You mention nested comments and that sound like require recursive query. — Juan Carlos Oropeza, Oct 09 '18 at 19:08
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/181596/discussion-between-nishanth--and-juan-carlos-oropeza). — Nɪsʜᴀɴᴛʜ ॐ, Oct 10 '18 at 07:28

Parfait · Answer 1 · 2018-10-09T16:59:33.987

Consider replacing the ON clause condition, AND ft.userid IN (SELECT u.ID ..., for a WHERE clause since this expression is set on a non-feed_item JOIN, specifically the users 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

See this thread on WHERE vs ON clause conditioning with LEFT JOIN.

score 1 · Answer 2 · answered Oct 09 '18 at 20:21

This is what I have. But because you desire result isnt clear I dont want spend much time trying to solve something may dont be what you need.

SQL DEMO

-- 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)
                );

Vinay · Answer 3 · 2019-09-10T06:34:17.110

The below query is used to fetch all the News Feed items

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

While iterating the value from the above executed SQL from the server side of the script i.e., PHP We will get all the comments related to post ID of the news feed

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

This Logic worked for me I had included more columns values if it is not necessary ignore keeping the rest. Taking single query will be much cumbersome

While this code may solve the question, [including an explanation](https://meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. — Artog, Sep 10 '19 at 06:11

Query to fetch comments from the news feed

Update

3 Answers3