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

Answer 1

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

Answer 2

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

Answer 3

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.