Как получить внуков или праправнуков родителей
У меня есть таблица как ниже
CREATE TABLE member
(
id VARCHAR(11) PRIMARY KEY NOT NULL,
referral_id VARCHAR(10) NOT NULL,
first_name VARCHAR(100) NOT NULL,
surname VARCHAR(100) NOT NULL,
address VARCHAR(100) NOT NULL,
country VARCHAR(20) NOT NULL,
phone VARCHAR(20) NOT NULL,
account_no VARCHAR(20) NOT NULL,
account_name VARCHAR(100) NOT NULL,
bank_name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
voucher_code VARCHAR(20) NOT NULL,
vcode_activated VARCHAR(2) NOT NULL,
current_level VARCHAR(2) NOT NULL,
extra_downlines VARCHAR(2) NOT NULL,
password VARCHAR(100) NOT NULL,
status VARCHAR(2) NOT NULL,
primary_downlines VARCHAR(2) NOT NULL,
date_registered VARCHAR(12) NOT NULL,
completed_level1 VARCHAR(2) NOT NULL,
referral_bonus VARCHAR(10) DEFAULT '0',
signup_earning VARCHAR(10) DEFAULT '0'
);
INSERT INTO member (id, referral_id, first_name, surname, address, country, phone, account_no, account_name, bank_name, email, voucher_code, vcode_activated, completed_level1, current_level, primary_downlines, extra_downlines, password, status, date_registered, referral_bonus, signup_earning) VALUES ('100000', '0', 'Mose', 'Alfi', 'MKd', 'NA', '098', '987', 'Alfi', 'gtb', 'al@yahc.com', '1504895239', '0', '1', '0', '', '1', 'a', '', '', '2000', '2000');
INSERT INTO member (id, referral_id, first_name, surname, address, country, phone, account_no, account_name, bank_name, email, voucher_code, vcode_activated, completed_level1, current_level, primary_downlines, extra_downlines, password, status, date_registered, referral_bonus, signup_earning) VALUES ('100001', '100000', 'Ush', 'Alim', 'Mkd', 'AD', '0949490', '0987', 'Ush', 'FBN', 'us@alim.com', '', '0', '', '0', '', '0', 'ali', '0', '', '0', '0');
INSERT INTO member (id, referral_id, first_name, surname, address, country, phone, account_no, account_name, bank_name, email, voucher_code, vcode_activated, completed_level1, current_level, primary_downlines, extra_downlines, password, status, date_registered, referral_bonus, signup_earning) VALUES ('100002', '100000', 'Tork', 'Alik', 'Mkd', 'AU', '0987', '098', 'Tor', 'Diamond', 'torku@alik.com', '', '0', '', '0', '', '0', 'ali', '0', '', '0', '0');
INSERT INTO member (id, referral_id, first_name, surname, address, country, phone, account_no, account_name, bank_name, email, voucher_code, vcode_activated, completed_level1, current_level, primary_downlines, extra_downlines, password, status, date_registered, referral_bonus, signup_earning) VALUES ('100003', '0', 'Te', 'Aliu', 'Gboko', 'AD', '09809', '798', 'Aliu Ter', 'Stanbic', 'ali@er.com', '', '0', '0', '1', '0', '0', 'ali', '0', '2017-09-11', '0', '0');
INSERT INTO member (id, referral_id, first_name, surname, address, country, phone, account_no, account_name, bank_name, email, voucher_code, vcode_activated, completed_level1, current_level, primary_downlines, extra_downlines, password, status, date_registered, referral_bonus, signup_earning) VALUES ('100004', '100000', 'Alex', 'Alis', 'mkd', 'NI', '9890', '99', 'Alex', 'Eco', 'alis@yshso.com', '', '0', '0', '1', '0', '0', 'alex', '0', '2017-09-11', '0', '0');
INSERT INTO
member (id, referral_id, first_name, surname, address, country, phone, account_no, account_name, bank_name, email, voucher_code, vcode_activated, completed_level1, current_level, primary_downlines, extra_downlines, password, status, date_registered, referral_bonus, signup_earning) VALUES ('100005', '100000', 'Alx', 'Alki', 'mkd', 'NI', '9890', '99', 'Alx', 'Eco', 'alki@yshso.com', '', '0', '0', '1', '0', '0', 'alex', '0', '2017-09-11', '0', '0');
В приведенной выше таблице участников id — это идентификатор участника, referral_id — это идентификатор участника, который ссылался на участника.
Член должен отослать 3 человек, которые станут членами, и каждый из них должен отослать 3 человек.
Как получить участника, который не ссылался на 3 человек?
Как мне также получить иерархическое отображение члена и людей, которых он упомянул?
Решение
Это выражение возвращает дочерних элементов (включая внуков) основного родителя (id 100000), которые имеют менее 3 собственных дочерних элементов.
SELECT `id`, `referral_id` AS `parent_id`, `children`
FROM (SELECT `id`, `referral_id`, (SELECT COUNT(`id`) FROM `member` AS `r` WHERE r.`referral_id` = m.`id`) AS `children` FROM `member` AS `m` ORDER BY `referral_id`, `id`) AS `members`,
(SELECT @pv := '100000') AS `init`
WHERE FIND_IN_SET(`referral_id`, @pv) > 0 AND @pv := CONCAT(@pv, ',', `id`)
HAVING `children` < 3
http://sqlfiddle.com/#!9/885878/4
Если у участника 3 или более детей, его внуки также будут исключены из результата.
SELECT `id`, `referral_id` AS `parent_id`, `children`
FROM (SELECT `id`, `referral_id`, (SELECT COUNT(`id`) FROM `member` AS `r` WHERE r.`referral_id` = m.`id`) AS `children` FROM `member` AS `m` HAVING `children` < 3 ORDER BY `referral_id`, `id`) AS `members`,
(SELECT @pv := '100000') AS `init`
WHERE FIND_IN_SET(`referral_id`, @pv) > 0 AND @pv := CONCAT(@pv, ',', `id`)
http://sqlfiddle.com/#!9/885878/5
В этом ответе вы можете найти дополнительную информацию о рекурсивных запросах.
https://stackoverflow.com/a/33737203/4020014
Другие решения
Один запрос, чтобы управлять ими всеми! Вот и ты, амиго!
SELECT * FROM blah WHERE id =
(SELECT rid
FROM blah
GROUP BY rid
HAVING COUNT(DISTINCT id) < 3);
Для первой части вашего вопроса, что следует сделать. Чтобы примерно объяснить запрос: нам нужен UNION запроса, который подсчитывает всех людей, которые указали больше, чем ноль, но меньше чем 3 человека И запрос, который перечислит всех тех, кто никого не отослал.
(SELECT referring.id,
GROUP_CONCAT(
CONCAT(referred.first_name, ' ', referred.surname)
) as Referred,
CONCAT(referring.first_name, ' ', referring.surname) as Referral,
COUNT(referred.id) as cnt
FROM member AS referred
LEFT JOIN member AS referring ON referring.id = referred.referral_id
GROUP BY referred.referral_id
HAVING cnt < 3)
UNION
(SELECT referring.id, 'nobody' as Referred,
CONCAT(referring.first_name, ' ', referring.surname) as Referral,
0 as cnt
FROM member AS referring
WHERE referring.id NOT IN (SELECT referral_id FROM member))
http://sqlfiddle.com/#!9/dc0b8/29
[ОБНОВИТЬ]Что касается второй части вашего вопроса, чтобы выполнить обход дерева в PHP, вы можете изменить запрос так, чтобы он также перечислял упомянутые идентификаторы:
(SELECT referring.id, GROUP_CONCAT(CONCAT(referred.first_name, ' ', referred.surname)) as Referred,
GROUP_CONCAT(referred.id) as Referred_ids,
CONCAT(referring.first_name, ' ', referring.surname) as Referral,
COUNT(referred.id) as cnt
FROM member AS referred
LEFT JOIN member AS referring ON referring.id = referred.referral_id
GROUP BY referred.referral_id
HAVING cnt < 3)
UNION
(SELECT referring.id, 'nobody' as Referred,
'null' as Referred_ids,
CONCAT(referring.first_name, ' ', referring.surname) as Referral,
0 as cnt
FROM member AS referring
WHERE referring.id NOT IN (SELECT referral_id FROM member) )
http://sqlfiddle.com/#!9/dc0b8/32
Идентификаторы потомков могут быть отправной точкой для построения древовидного представления вашей иерархии. Но есть большое НО.
Представление иерархических данных в MySQL на самом деле довольно сложная тема. То, как вы представляете свои данные, называется списком смежности и представляет собой простейшую форму, но она вызывает много проблем с извлечением и управлением деревом (добавление удаления данных и т. Д.). По мнению многих это антипаттерн.
В вашем конкретном случае проблемы с поиском могут быть еще более острыми, поскольку ваше реферальное дерево будет довольно глубоким. Запрашивать список смежности, представляющий дерево, у которого нет фиксированного количества уровней вложенности, сложно.
Посмотрите на эту замечательную статью, чтобы выяснить проблемы, которые могут возникнуть перед вами, и возможные решения (альтернативное представление в БД)
detector