How to Select Related Posts (in Something Similar to Facebook's Wall)?

Question

I have something like Facebook's Wall build on PHP that uses MySQL database.

Structure:

• Categories are made by admins and aren't meant to be changed often,
• Groups are made by users. Count of them can be very huge. They are like "child" of one category,
• Posts are made by users too. They are like "child" of one group and that group is like "child" of one category;

Last thing is comments. They are stored in the same table where posts are, but with "reply" row set to post's ID that's like "parent" of it.

Here are simple example:

Food (category):

-> Kebabs (group)

->-> What's your fave kebab, folks? (post)

->->-> I love doner kebab! (post too, but displayed as comment)

I have troubles when selecting related posts. You see, I need to display posts that are somehow related to user.

These relationships at the moment are:

• User is author of post,
• User has commented on post (that post may not be made by himself),
• Another user is commented on group where "our" user is member;

I guess that will be one complex query... and my knowledge is little too short.

Here are the query:

SELECT `posts`.`id`, `posts`.`created_at`, `posts`.`content`, `posts`.`replies`, `groups`.`id` AS `group_id`, `groups`.`name` AS `group_name`, `users`.`name`, `users`.`surname`, `users`.`avatar`
FROM `posts`
JOIN `groups` ON (`groups`.`id` = `posts`.`group_id`)
JOIN `users` ON (`users`.`id` = `posts`.`user_id`)
WHERE `posts`.`status` = 1 AND `posts`.`post_id` = 0 AND `posts`.`user_id` = '33'
ORDER BY `posts`.`id` DESC
LIMIT 10
OFFSET 0

Edit:

Here is the table structure:

CREATE TABLE `bio_community_categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

CREATE TABLE `bio_community_groups` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `created_at` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;

CREATE TABLE `bio_community_posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `group_id` int(10) unsigned NOT NULL,
  `post_id` int(11) unsigned NOT NULL DEFAULT '0',
  `created_at` int(11) NOT NULL,
  `content` text NOT NULL,
  `status` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `replies` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `status` (`status`),
  KEY `post_id` (`post_id`),
  KEY `user_id` (`user_id`),
  KEY `group_id` (`group_id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `surname` varchar(50) NOT NULL,
  /* Etc.. */
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `bio_community_categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

CREATE TABLE `bio_community_groups` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `created_at` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;

CREATE TABLE `bio_community_posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `group_id` int(10) unsigned NOT NULL,
  `post_id` int(11) unsigned NOT NULL DEFAULT '0',
  `created_at` int(11) NOT NULL,
  `content` text NOT NULL,
  `status` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `replies` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `status` (`status`),
  KEY `post_id` (`post_id`),
  KEY `user_id` (`user_id`),
  KEY `group_id` (`group_id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `surname` varchar(50) NOT NULL,
  /* Etc.. */
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

Assume there will be group user relation table too, Let us say "groups_members" so your query will be as follows,

SELECT `posts`.`id`, `posts`.`created_at`, `posts`.`content`, `posts`.`replies`, `groups`.`id` AS `group_id`, `groups`.`name` AS `group_name`, `users`.`name`, `users`.`surname`, `users`.`avatar`
FROM `posts`
JOIN `groups` ON (`groups`.`id` = `posts`.`group_id`)
JOIN `users` ON (`users`.`id` = `posts`.`user_id`)
WHERE `posts`.`status` = 1 AND `posts`.`post_id` = 0 AND  (`posts`.`user_id` = '33' OR `bio_community_posts`.`group_id` IN (SELECT `group_id` FROM `groups_members` WHERE `user_id` =33)) 
ORDER BY `posts`.`id` DESC
LIMIT 10
OFFSET 0

From the above query you will get the posts posted by the user 33 + those posts from the group in which the user 33 is a member.