Поиск напарников по недостающим навыкам и наоборот.

[RedLight]

Требуется сделать поиск напарников.
Например будут навыки: PHP, Laravel, JavaScript, Angular, HTML.
Каждый навык может иметь пятибалльную оценку или 0 если не указан.
Например программист ищет напарника:
1. Сам о себе указывает: PHP = 5, Laravel = 3, JavaScript = 1, Angular = 1, HTML = 2.
2. Хочет найти напарника: PHP = 1, Laravel = 1|5, JavaScript = 4|5, Angular = 4|5, HTML = 0 (любой).
Какие таблицы создать и как поиск организовать в MySQL или где бы почитать ближе к теме?

Сообщение отредактировано: RedLight - 23.03.23, 12:56

[Akina]

Читать про фасетный поиск.
Структура - EAV, multicolumn (только для статического набора скиллов), SET datatype. Возможно, JSON, но тут есть сомнения..

[RedLight]

Прономеруем навыки и назовём оценки:

// Массив названий навыков для полей формы.
$skills = [1 => 'PHP', 2 => 'Laravel', 3 => 'JavaScript', 4 => 'Angular', 5 => 'HTML'];
// Массив названий уровней навыков для radio (собственный) и chekbox (искомые).
$levels = [0 => 'unkown', 1 => 'fail', 2 => 'bad', 3 => 'normal', 4 => 'good', 5 => 'excellent'];

Создадим таблицу пользователей со своими навыками и масками искомых навыков:

-- Таблица пользователей.
CREATE TABLE `users`
(
    -- Идентификатор пользователя.
    `user` TINYINT UNSIGNED NOT NULL,
    -- Идентификатор навыка.
    `skill` TINYINT UNSIGNED NOT NULL,
    -- Собственный уровень навыка.
    `have` TINYINT UNSIGNED NOT NULL,
    -- Маска искомых навыков.
    `need` TINYINT UNSIGNED NOT NULL
);

Добавим пользователя из примера и двух других, подходящего и не подходящего.

INSERT INTO `users`(`user`, `skill`, `have`, `need`) VALUES
-- Пользователь из примера, но первый искомый навык только 1.
(1, 1, 5,  1), -- имеется 5, требуется 1
(1, 2, 3, 17), -- имеется 3, требуется 1 или 5
(1, 3, 1, 24), -- имеется 1, требуется 4 или 5
(1, 4, 1, 24), -- имеется 1, требуется 4 или 5
(1, 5, 2,  0), -- имеется 2, требуется
-- Взаимно подходящий пользователь.
(2, 1, 1, 16), -- имеется 1, требуется 5
(2, 2, 1, 14), -- имеется 1, требуется 2, 3 или 4
(2, 3, 4, 17), -- имеется 4, требуется 1 или 5
(2, 4, 5,  1), -- имеется 5, требуется 1
(2, 5, 2,  0), -- имеется 2, не требуется
-- Неподходящий пользователь - копия первого.
(3, 1, 5,  1), -- имеется 5, требуется 1
(3, 2, 3, 17), -- имеется 3, требуется 1 или 5
(3, 3, 1, 24), -- имеется 1, требуется 4 или 5
(3, 4, 1, 24), -- имеется 1, требуется 4 или 5
(3, 5, 0,  0); -- не указан, требуется любой

Создадим таблицу масок искомых навыков:

-- Таблица масок.
CREATE TABLE `masks`
(
    -- Номер маски.
    `mask` TINYINT UNSIGNED NOT NULL,
    -- Одно из значений маски.
    `item` TINYINT UNSIGNED NOT NULL
);

Добавим используемые в примере маски.

INSERT INTO `masks`(`mask`, `item`) VALUES
-- 0b00001 =  1
( 1, 1),
-- 0b01110 = 14
(14, 2),
(14, 3),
(14, 4),
-- 0b10000 = 16
(16, 5),
-- 0b10001 = 17
(17, 1),
(17, 5),
-- 0b11000 = 24
(24, 4),
(24, 5);

Если таким образом таблицы организовать то можно будет запрос поиска составить или так не бывает?
Не будет ли такая конструкция очень медленной?
Как составить запрос?

Сообщение отредактировано: RedLight - 23.03.23, 16:20

[Majestio]

Цитата RedLight @ 22.03.23, 12:08

Какие таблицы создать и как поиск организовать в MySQL или где бы почитать ближе к теме?

Оптимальный вариант будет - три таблицы:

• users (id, name)
• skills (id, name)
• user2skill (user_id, skill_id, level)

Третьей таблицей ты связываешь пользователя с навыком, при связывании указываешь численный уровень. Компактный вариант и ничего лишнего.
Запросы на поиск по любым критериям делаются простейшими соединениями (joins).

[RedLight]

Majestio, в такой таблице user2skill навыки не разделяются на собственные и требуемые.

[Majestio]

Цитата RedLight @ 24.03.23, 08:32

Majestio, в такой таблице user2skill навыки не разделяются на собственные и требуемые.

В данной таблице - только собственные. Если нужны еще и требуемые, есть два варианта:
1) сделать еще такую же таблицу для требуемых
2) не делать доп. таблицу, а добавить поле в user2skill, которое будет признаком "скилл собственный или который требуется"

[Akina]

Возможная реализация:

CREATE TABLE user (user_id INT AUTO_INCREMENT PRIMARY KEY, user_name VARCHAR(255));
 
CREATE TABLE skill (skill_id INT AUTO_INCREMENT PRIMARY KEY, skill_name VARCHAR(255));
 
CREATE TABLE user_skill (
  user_id INT NOT NULL,
  skill_id INT NOT NULL,
  PRIMARY KEY (user_id, skill_id),
  FOREIGN KEY (user_id) REFERENCES user (user_id),
  FOREIGN KEY (skill_id) REFERENCES skill (skill_id),
  grade TINYINT NOT NULL,
  CHECK (grade BETWEEN 1 AND 5)
  );
 
CREATE TABLE criteria (
  user_id INT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES user (user_id),
  skill_id INT NOT NULL,
  FOREIGN KEY (skill_id) REFERENCES skill (skill_id),
  min_grade TINYINT NOT NULL,
  max_grade TINYINT NOT NULL,
  CHECK (min_grade <= max_grade),
  CHECK (min_grade >= 1),
  CHECK (max_grade <= 5)
  );

Запрос на получение партнёров, соответствующих всем критериям:

SELECT user.user_name
FROM user_skill
JOIN user USING (user_id)
JOIN criteria USING (skill_id)
WHERE user_skill.grade BETWEEN criteria.min_grade AND criteria.max_grade
  AND user_skill.user_id <> criteria.user_id
GROUP BY user_skill.user_id
HAVING COUNT(*) = 5;

DEMO fiddle

В запросе жёстко забито именно 5 грейдов. По-хорошему, надо, конечно, посчитать COUNT записей в таблице скиллов в подзапросе.

В принципе подобная схема вполне допускает различные расширения. Например, в таблицу критериев можно ввести весовые коэффициенты скиллов и отбирать тех партнёров, которые показали требуемый суммарный уровень скиллов... в общем, всё упирается исключительно в фантазию разработчика.

Сообщение отредактировано: Akina - 24.03.23, 10:57

[Majestio]

Амигос, вся эта затея, скажем так, разбивается при совсем небольшом усложнении требований по поиску.

Пример:

найти напарника, у которого PHP=3 и Laravel=3 или Javascript=5 или PHP>=4

Я вижу пока один выход - развернуть строки в столбцы (pivot) и по ним строить сложные условия.
Для фиксированного количества разворачиваемых столбцов скилов - вроде не сложно, а вот для произвольного - в MySQL я пока не знаю как.

[Akina]

Цитата Majestio @ 24.03.23, 11:13

а вот для произвольного - в MySQL я пока не знаю как.

Очевидно, что для не заданных скиллов просто дефолтно задаётся любое значение - от 1 до 5. То есть Javascript=5 или PHP>=4 разворачивается в PHP = 4-5, Laravel = 1-5, JavaScript = 5-5, Angular = 1-5, HTML = 1-5.

Либо в запрос добавляется условие, что исследуются только заданные скиллы (WHERE .. AND skill_id IN (1,3)) и корректируется количество в HAVING.

В общем, про фантазию я уже говорил..

Сообщение отредактировано: Akina - 24.03.23, 12:10

[RedLight]

Akina, вообще я подразумевал набор "1 или 5" для Laravel, а не интервал "от 1 до 5", но с интервалами тоже полезно, это у меня следующая задача.

Цитата Majestio @ 24.03.23, 10:25

2) не делать доп. таблицу, а добавить поле в user2skill, которое будет признаком "скилл собственный или который требуется"

Видимо надо столбец `have` таблицы `users` в отдельную таблицу перенести.

Сообщение отредактировано: RedLight - 24.03.23, 14:51

[Akina]

Цитата RedLight @ 24.03.23, 13:13

вообще я подразумевал набор "1 или 5" для Laravel

С практической точки зрения это проверка типа "А рельсу твоя бензопила распилит?". Ну не могу я придумать реального случая, когда к скиллу работника предъявлялись бы требования из разряда "всё или ничего" - тем более с учётом изрядной субъективности этой оценки. Хотя я всё равно не вижу проблемы - и даже в том, чтобы реализовать подобное требование.

[Majestio]

Цитата Akina @ 24.03.23, 12:09

Очевидно, что для не заданных скиллов просто дефолтно задаётся любое значение - от 1 до 5.

Не в этом вопрос. Вопрос в том, что количество скилов - произвольно. Тут засада. Жестоко заданные CASE и WHEN не решают вопрос универсально.

Добавлено 25.03.23, 09:43
Я повторюсь: решите мой ранее заданный вопрос. А потом в рамках неопределенного заранее набора скилов.

[Akina]

Цитата Majestio @ 25.03.23, 09:41

Жестоко заданные CASE и WHEN не решают вопрос универсально.

Во-первых, в моём запросе никаких CASE нет.
Во-вторых, как я сказал, запрос должен сравнивать не с количеством скиллов вообще, а с количеством скиллов в запросе - то есть убеждаться, что каждое условие запроса выполняется.
В третьих, для уж совсем универсальности, с такими дикими запросами, можно от схемы min_skill-max_skill перейти на схему ckill_set, и переданный набор требований по уровню скилла описывать в сериализованном виде (да хоть бы и в CSV).

Цитата Majestio @ 25.03.23, 09:41

Я повторюсь: решите мой ранее заданный вопрос. А потом в рамках неопределенного заранее набора скилов.

Дайте то, с чем можно работать. Сформулируйте задачу полностью и предоставьте все необходимые для её решения материалы (fiddle с исходными данными, точную формулировку запроса, эталонный ответ, подробные пояснения).

[Majestio]

Akina, не злись Все в рамках обсуждения. На счет "дайте материалы" - дай немножко времени, все будет

[Akina]

Majestio
Сорри, я почему-то подумал, что именно ты автор вопроса.