Codeigniter Join Table to Showing Different Content Results

Question

I have 7 tables to store user data such posts, images, updates, comments, likes, reposts and user itself.

And here is my questions: How to using right query to execute join table? I'm using this query:

if ( ! function_exists('getTimeline'))
{
    function getTimelines($contributor = null, $limit = 10, $offset = 0)
    {
        $CI =& get_instance();
        $CI->db->select('
            abycms_posts.*,
            abycms_images.imageID,
            abycms_images.contributor as owner,
            abycms_images.imageContent,
            abycms_images.imageFile,
            abycms_images.imageSlug,
            abycms_images.timestamp as date,
            abycms_images.visits_count as visits,
            abycms_updates.updateID,
            abycms_updates.userID as updater,
            abycms_updates.updateContent,
            abycms_updates.visibility,
            abycms_updates.timestamp as update_time,
            abycms_likes.likeID,
            abycms_likes.userID as userLike,
            abycms_likes.type as likeType,
            abycms_likes.timestamp as like_time,
            abycms_comments.commentID,
            abycms_comments.userID as commentUser,
            abycms_comments.type as commentType,
            abycms_comments.timestamp as comment_time,
            abycms_reposts.repostID,
            abycms_reposts.userID as repostUser,
            abycms_reposts.type as repostType,
            abycms_reposts.timestamp as repost_time
        ');
        $CI->db->from('abycms_users');
        $CI->db->join('abycms_posts', 'abycms_posts.contributor = abycms_users.userID', 'left');
        $CI->db->join('abycms_images', 'abycms_images.contributor = abycms_users.userID', 'left');
        $CI->db->join('abycms_updates', 'abycms_updates.userID = abycms_users.userID', 'left');
        $CI->db->join('abycms_likes', 'abycms_likes.userID = abycms_users.userID', 'left');
        $CI->db->join('abycms_comments', 'abycms_comments.userID = abycms_users.userID', 'left');
        $CI->db->join('abycms_reposts', 'abycms_reposts.userID = abycms_users.userID', 'left');
        $CI->db->where('abycms_users.userID', $contributor);
        $CI->db->limit($limit, $offset);

        // How to order results by newest `timestamp` for posts, images, updates, comments, likes or reposts?
        $CI->db->order_by('abycms_posts.timestamp', 'desc');

        // How to handle not duplicate `postID` or `imageID` also group it by different `type`s?
        $CI->db->group_by('abycms_posts.postID, abycms_images.imageID'); 

        $query = $CI->db->get();

        if($query->num_rows() > 0)
        {
            return $query->result_array();
        }
        else
        {
            return array();
        }
    }
}

And there is my view to handle results in different type:

foreach(getTimelines($page['userID'], $limit, $offset) as $row)
{
    if($row['updateID'] != null) // Updating Status
    {
        // This status updates
    }
    elseif($row['postID'] != null) // Writing Article
    {
        // This is posts
    }
    elseif($row['imageID'] != null) // Uploading Image
    {
        // This is images
    }
    elseif($row['commentID'] != null) // Commented on Post
    {
        // This is comments
    }
    elseif($row['likeID'] != null) // Liking User Post
    {
        // This is likes
    }
    elseif($row['repostID'] != null) // Reposting User Post
    {
        // This is reposts
    }
}

When i'm using above query, results is showing up but i have no idea to separate content types. It always shown as status updates, and all unique id such postID, imageID, updateID, repostID, likeID and commentID have same value.

Answer 1

The query is generating a partial cross product.

For every row returned from _users, MySQL is getting all of the matching rows from _likes.

For sake of an example, we'll assume that there is one row being returned from _users, and there are four matching rows in _likes, returning (so far) a total of four rows. The row from _users gets matched to each of the four rows from _likes. All of the columns from the row from _users is duplicated into each of the four rows.

And from the _posts table, for the sake of an example, we'll assume that there are two rows that match. So each of those two rows returned from _posts is going to matched to each of the four rows we already have, giving us a total of eight rows. (Every row returned from _posts is matched with every row returned from _likes.)

From the _comments table, for this example, let's say there are six rows returned. Each of those rows gets matched with the eight rows we already have, giving us a total of 48 rows. And a lot of values from the columns of each table is getting "duplicated" into new rows, as multiple rows from the new tables are joined in.

And so on, with each additional joined table.

It's a partial "cross product" of the tables. (A semi-Cartesian product?)

---

If you want to return distinct list of _posts, a distinct list of _likes, and distinct list of _comments, etc. then you could run a separate query for each table. That would avoid the "duplication" that happens due to the join operation. That's probably the simplest approach.

Otherwise, if you want to get a distinct list of _posts, _likes, _comments, et al. out of the resultset the current query is returning, you'd need the client to sift through the rows to filter out the duplicated _posts, _likes, _comments. You'd need to have a unique identifier for each of those tables included in the returned rows.

Basically, your code would need to build separate arrays for _posts, _likes, _comments. For each row from the resultset, you'd need to check whether the values from the _posts columns were from a row from _posts you've already processed. If it's one you've already processed, discard it, otherwise, add it to the array. Essentially de-duplicating the rows down into separate results from each table, in the form that you'd get from a separate query of each table.