Как получить информацию о группе вместе с деталями участников и общими друзьями (присутствующими в группе)

У меня четыре таблицы следующим образом

• таблица пользователей

  

• таблица group_types

  • PHP / MySQL: получение нескольких столбцов с одинаковым именем в объединении запросов без псевдонимов?
  • Eloquent Left Join, получивший неожиданный результат
  • codeigniter select join дважды из той же таблицы
  • WordPress - Получить количество сообщений и комментариев по дням
  • Получать значения столбцов одноранговой сети из одной таблицы с помощью Codeigniter и MySQL

  

• группы Таблица

  

• друзья Таблица

  

• Таблица group_users

  

И представление отношения следующим образом

Я хотел бы получить все данные группы, с именами групп, именами членов группы, типом группы и подсчетами друзей пользователей (друзей, присутствующих в группе)

SAMPLE JSON OUTPUT ДЛЯ USER_ID: 1

{ group_name : "second", group_type : "private", group_members : "Minion,Ben", friends_count : "1" } { group_name : "fourth", group_type : "public", group_members : "Minion", friends_count : "0" } 

ОБЪЯСНЕНИЕ

• для user_id 1 существует три group_ids, из которых активны только две (2,4) группы, а одна (5) неактивна. Такие имена групп будут SECOND, FOURTH и соответствующие типы групп будут PRIVATE, PUBLIC.

• Группа 2 имеет два активных члена (1 и 2), а группа 4 имеет только один член (1).

• Пользователь 1 друзья (друзья-миньоны), присутствующие в группе 2, являются (2,5), из которых активен user_id 2, поэтому количество друзей будет равно 1.

• аналогично для группы 4, количество друзей будет равно 0.

Я пробовал следующие два запроса

сначала получить информацию о группе

 select group_concat(uls.name) "username", gls.name "group name",gtp.name "group type",gus.status from users uls inner join groups gls inner join group_types gtp inner join group_users gus on uls.user_id=gus.user_id and gls.group_id=gus.group_id and gtp.type_id=gls.type_id where gus.status='active' group by gls.name, gtp.name, gus.status 

получить информацию о друзьях с другим запросом Затем отфильтровать оба результата запроса

ВОПРОС: Как я могу это достичь? Есть ли другой способ сделать это, используя объединения

SQL DUMP FILE

 -- -- Table structure for table `friends` -- CREATE TABLE `friends` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `friend_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `friends` -- INSERT INTO `friends` (`id`, `user_id`, `friend_id`) VALUES (1, 1, 2), (2, 1, 3), (3, 5, 1), (4, 3, 5); -- -------------------------------------------------------- -- -- Table structure for table `groups` -- CREATE TABLE `groups` ( `group_id` int(6) NOT NULL, `name` varchar(20) NOT NULL, `type_id` int(6) NOT NULL, `user_id` int(6) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `groups` -- INSERT INTO `groups` (`group_id`, `name`, `type_id`, `user_id`) VALUES (1, 'first', 1, 5), (2, 'second', 2, 2), (3, 'third', 1, 2), (4, 'fourth', 1, 4), (5, 'five', 1, 3); -- -------------------------------------------------------- -- -- Table structure for table `group_type` -- CREATE TABLE `group_type` ( `type_id` int(6) NOT NULL, `name` varchar(30) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `group_type` -- INSERT INTO `group_type` (`type_id`, `name`) VALUES (1, 'public'), (2, 'private'); -- -------------------------------------------------------- -- -- Table structure for table `group_users` -- CREATE TABLE `group_users` ( `user_id` int(6) NOT NULL, `group_id` int(6) NOT NULL, `status` varchar(10) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `group_users` -- INSERT INTO `group_users` (`user_id`, `group_id`, `status`) VALUES (1, 2, 'active'), (5, 2, 'pending'), (2, 2, 'active'), (2, 3, 'pending'), (2, 5, 'active'), (1, 4, 'active'), (1, 5, 'pending'); -- -------------------------------------------------------- -- -- Table structure for table `users` -- CREATE TABLE `users` ( `user_id` int(6) NOT NULL, `name` varchar(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `users` -- INSERT INTO `users` (`user_id`, `name`) VALUES (1, 'Minion'), (2, 'Ben'), (3, 'Lakki'), (4, 'Bob'), (5, 'Ananth'); -- -- Indexes for dumped tables -- -- -- Indexes for table `friends` -- ALTER TABLE `friends` ADD PRIMARY KEY (`id`), ADD KEY `inviter_id` (`user_id`), ADD KEY `invitee_id` (`friend_id`); -- -- Indexes for table `groups` -- ALTER TABLE `groups` ADD PRIMARY KEY (`group_id`), ADD KEY `type_id` (`type_id`); -- -- Indexes for table `group_type` -- ALTER TABLE `group_type` ADD PRIMARY KEY (`type_id`); -- -- Indexes for table `group_users` -- ALTER TABLE `group_users` ADD KEY `user_id` (`user_id`), ADD KEY `group_id` (`group_id`); -- -- Indexes for table `users` -- ALTER TABLE `users` ADD PRIMARY KEY (`user_id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `friends` -- ALTER TABLE `friends` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5; -- -- AUTO_INCREMENT for table `groups` -- ALTER TABLE `groups` MODIFY `group_id` int(6) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6; -- -- AUTO_INCREMENT for table `group_type` -- ALTER TABLE `group_type` MODIFY `type_id` int(6) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; -- -- AUTO_INCREMENT for table `users` -- ALTER TABLE `users` MODIFY `user_id` int(6) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6; /*!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 */;