Блог

Пожалуйста, помогите мне, я пытался, но не смог найти правильный ответ. У меня есть 5 таблиц users, profile, questions, question_responses and profile_responses . Пользователи должны предоставлять ответы на такие вопросы, как

1. регион (Онтарио, Британская Колумбия и т. Д.),
2. этническая принадлежность (белый, китаец, южноазиатец и т. Д.)
3. жанр (Еда, Здоровье, мода и т. Д.)
4. страна (Канада, Великобритания, США).

таблица profile_responses имеет отношения «многие ко многим» с профилем, ответами и profile_responses. Ниже приведены таблицы.

 CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  'email' varchar(300) NOT NULL,
  'password' varchar(300) NOT NULL,
   'name' varchar(300) NOT NULL,
   'user_type' varchar(10) NOT NULL,
);

INSERT INTO `users` (`id`, `email`, `password`, `name`, `user_type`) VALUES 
(8, 'test1@gmail.com', '', 'Test Name1', 'Influencers'),
(14, 'test1@gmail.com', '', 'Test Name2', 'Influencers'),
(15, 'test1@gmail.com', '', 'Test Name3', 'Influencers');



CREATE TABLE `profile` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `name` varchar(500) NOT NULL,
  `instagram_handle` varchar(1000) NOT NULL,
   `comments` text NOT NULL
);
 
INSERT INTO `profile` (`id`, `user_id`, `name`, `instagram_handle`,`comments`) VALUES
(1, 8, 'Test Name1', 'testhandle1', 'Test Comments'),
(10, 14, 'Test Name2', 'testhandle2', 'Test Comments'),
(12, 15, 'Test Name3 ', 'testhandle3', 'Test Comments');



questions
CREATE TABLE `questions` (
  `id` int(11) NOT NULL,
  `text` varchar(50) NOT NULL,
  `active` int(11) NOT NULL
);
 
INSERT INTO `questions` (`id`, `text`, `active`) VALUES
(1, 'country', 1),
(2, 'ethnicity', 1),
(3, 'region', 1),
(4, 'genre', 1);



question_responses
CREATE TABLE `question_responses` (
  `id` int(11) NOT NULL,
  `question_id` int(11) NOT NULL,
  `text` varchar(300) NOT NULL,
  `abbr` varchar(30) NOT NULL,
  `active` tinyint(4) NOT NULL,
);

INSERT INTO `question_responses` (`id`, `question_id`, `text`, `abbr`, `active`, `sort_order`) VALUES
(1, 1, 'USA', 'US', 1, 0),
(2, 1, 'UK', 'UK', 1, 0),
(3, 1, 'Canada', 'CA', 1, 0),
(7, 2, 'South Asian', 'SA', 1, 0),
(8, 2, 'Chinese', 'CH', 1, 0),
(9, 2, 'White', 'W', 1, 0),
(13, 4, 'Lifestyle', 'Lifestyle', 1, 0),
(14, 4, 'Fashion', 'Fashion', 1, 0),
(15, 4, 'Food', 'Food', 1, 0),
(16, 4, 'Health amp; Fitness', 'Health', 1, 0),
(18, 3, 'Britich Columbia', 'BC', 1, 6),
(19, 3, 'Ontario', 'ON', 1, 1),
(20, 3, 'Alberta', 'AB', 1, 9),
(23, 3, 'Taxes', 'TX', 1, 52),
(24, 3, 'California', 'CA', 1, 15);


profile_responses
CREATE TABLE `profile_responses` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `response_id` int(11) NOT NULL,
  FOREIGN KEY (user_id) REFERENCES profile(user_id) ON UPDATE CASCADE,  
  FOREIGN KEY (response_id) REFERENCES question_responses(id) ON UPDATE CASCADE
);

INSERT INTO `profile_responses` (`id`, `user_id`, `response_id`) VALUES 
(1, 8, 3),
(2, 8, 18),
(3, 8, 15),
(4, 8, 9),
(5, 14, 1),
(6, 14, 23),
(7, 14, 16),
(8, 14, 9),
(9, 15, 3),
(10, 15, 19),
(11, 15, 14),
(12, 15, 7);
  

Я пытаюсь отобразить ответы каждого пользователя в одной строке следующим образом

 Name            Region      Ethnicity           Genre                   Country
Test Name1      BC              White           Food                    Canada
Test Name2      Taxes           White           Health amp; Fitness        USA
Test Name3      Ontario         South Asian     Fashion                 Canada
  

Для решения я создал два запроса. Сначала я выясняю, сколько пользователей ответили на вопросы. Поскольку ответы различаются, поэтому N (количество) основано на наборе ответов. Этот запрос работает нормально.

 SELECT pro.user_id
FROM profile_responses as pr
INNER JOIN profile as pro ON pro.user_id = pr.user_id
WHERE pr.response_id IN (3,18,15,23,16,19,14,7) 
GROUP BY pro.user_id
HAVING COUNT(DISTINCT pr.response_id) >= N
  

Для отображения результатов я попробовал следующее, которое работает, но если у меня есть 10 вопросов, тогда я должен написать десять комбинаций? Пожалуйста, помогите мне, я не эксперт.

 select us1.id, us1.email, up1.name, up1.instagram_handle, qr.abbr as region, qr1.abbr as ethnicity, up1.followers,qr2.abbr as genre, qr3.abbr as country, up1.comments
FROM users as us1
LEFT JOIN profile as up1 ON up1.user_id = us1.id

LEFT JOIN profile_responses usrep ON up1.user_id = usrep.user_id 
JOIN question_responses qr ON usrep.response_id = qr.id AND qr.question_id = 3
JOIN questions qs ON qs.id = qr.question_id

LEFT JOIN profile_responses usrep1 ON up1.user_id = usrep1.user_id 
JOIN question_responses qr1 ON usrep1.response_id = qr1.id AND qr1.question_id = 2
JOIN questions qs1 ON qs1.id = qr1.question_id

LEFT JOIN profile_responses usrep2 ON up1.user_id = usrep2.user_id 
JOIN question_responses qr2 ON usrep2.response_id = qr2.id AND qr2.question_id = 4
JOIN questions qs2 ON qs2.id = qr2.question_id

LEFT JOIN profile_responses usrep3 ON up1.user_id = usrep3.user_id 
JOIN question_responses qr3 ON usrep3.response_id = qr3.id AND qr3.question_id = 1
JOIN questions qs3 ON qs3.id = qr3.question_id

WHERE us1.user_type="Influencers" AND us1.id IN (8,14,15)
GROUP BY us1.id