从三个表中进行选择——mysql和php

Question

我有下面三张表，希望从中获得以下选择：

1。）从collaboration中选择所有项目，并在group_members中按group_id对给定用户进行分组
这意味着当用户登录时，他将看到属于他所属组的所有（且仅限于）collaboration项目

2.）对于每个组id，选择所有协作项目
这意味着当用户从上面的1中选择任何组（group_id）时，他将看到属于所选组（group_id）的所有collaboration项目

约束：每个用户必须是组成员。users表用于提供用户的firstname和lastname

这是我尝试过的，但是没有用

    function OrderByGroup_id($username) {
      $data = array();
      $currenttime = time();
      $q = "
      SELECT *
      FROM collaboration
      INNER JOIN group_members ON collaboration.group_id = group_members.group_id
      INNER JOIN users ON users.username = group_members.username
      WHERE collaboration.parent_id IS NULL and collaboration.is_comment = 0 
      AND group_members.username = :user group by collaboration.group_id";
      $sq = $this->connection->prepare($q);
      $sq->execute(array(':user' => $username));
    while($row = $sq->fetch()) {
      $json = array();
      $json['title'] = $row['title'];
      $json['question'] = $row['content'];
      $json['firstname'] = $row['firstname'];
      $json['lastname'] = $row['lastname'];
      $json['timestamp'] = $row['timestamp'];
      $json['key'] = $row['group_id'];
      $data[] = $json;
     }
      $allposts =json_encode($data);
      return $allposts= json_decode($allposts, true);
    }

这是桌子

CREATE TABLE IF NOT EXISTS `collaboration` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `group_id` varchar(255) DEFAULT NULL,
  `author` varchar(30) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `content` text NOT NULL,
  `is_comment` tinyint(1) unsigned NOT NULL,
  `file` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `points` int(11) DEFAULT NULL,
  `timestamp` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `users` (
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  `username` varchar(30) NOT NULL,
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `group_members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(30) NOT NULL,
  `group_id` varchar(50) NOT NULL,
  `status` tinyint(1) unsigned NOT NULL,
  `timestamp` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

谢谢你的意见！

Answer 1

以下选择确实提供了问题1的请求。然而，我知道查询不是最优的，因为我确实选择了目前不需要的条目。例如，*
我只想选择标题，内容，时间戳，组id，从协作和名字，名字，从用户

    
    SELECT * FROM collaboration
    INNER JOIN group_members ON collaboration.group_id = group_members.group_id
    INNER JOIN users ON users.username = collaboration.author
    WHERE collaboration.parent_id IS NULL and collaboration.is_comment = 0 
    AND group_members.username = :user 
    GROUP BY collaboration.group_id
    ORDER BY collaboration.timestamp DESC