На главную Наши проекты:
Журнал   ·   Discuz!ML   ·   Wiki   ·   DRKB   ·   Помощь проекту
ПРАВИЛА FAQ Помощь Участники Календарь Избранное RSS
msm.ru
! информация о разделе
user posted imageДанный раздел предназначается исключительно для обсуждения вопросов использования языка запросов SQL. Обсуждение общих вопросов, связанных с тематикой баз данных - обсуждаем в разделе "Базы данных: общие вопросы". Убедительная просьба - соблюдать "Правила форума" и не пренебрегать "Правильным оформлением своих тем". Прежде, чем создавать тему, имеет смысл заглянуть в раздел "Базы данных: FAQ", возможно там уже есть ответ.

Модераторы: Akina
Страницы: (2) [1] 2  все  ( Перейти к последнему сообщению )  
> Поиск напарников по недостающим навыкам и наоборот.
    Требуется сделать поиск напарников.
    Например будут навыки: 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 -
      Читать про фасетный поиск.
      Структура - EAV, multicolumn (только для статического набора скиллов), SET datatype. Возможно, JSON, но тут есть сомнения..
        Прономеруем навыки и назовём оценки:
        ExpandedWrap disabled
          // Массив названий навыков для полей формы.
          $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'];

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

        Добавим пользователя из примера и двух других, подходящего и не подходящего.
        ExpandedWrap disabled
          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); -- не указан, требуется любой

        Создадим таблицу масок искомых навыков:
        ExpandedWrap disabled
          -- Таблица масок.
          CREATE TABLE `masks`
          (
              -- Номер маски.
              `mask` TINYINT UNSIGNED NOT NULL,
              -- Одно из значений маски.
              `item` TINYINT UNSIGNED NOT NULL
          );

        Добавим используемые в примере маски.
        ExpandedWrap disabled
          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);

        Если таким образом таблицы организовать то можно будет запрос поиска составить или так не бывает?
        Не будет ли такая конструкция очень медленной?
        Как составить запрос?
        :scratch:
        Сообщение отредактировано: RedLight -
          Цитата RedLight @
          Какие таблицы создать и как поиск организовать в MySQL или где бы почитать ближе к теме?

          Оптимальный вариант будет - три таблицы:
          • users (id, name)
          • skills (id, name)
          • user2skill (user_id, skill_id, level)
          Третьей таблицей ты связываешь пользователя с навыком, при связывании указываешь численный уровень. Компактный вариант и ничего лишнего.
          Запросы на поиск по любым критериям делаются простейшими соединениями (joins).
            Majestio, в такой таблице user2skill навыки не разделяются на собственные и требуемые.
            :-?
              Цитата RedLight @
              Majestio, в такой таблице user2skill навыки не разделяются на собственные и требуемые.

              В данной таблице - только собственные. Если нужны еще и требуемые, есть два варианта:
              1) сделать еще такую же таблицу для требуемых
              2) не делать доп. таблицу, а добавить поле в user2skill, которое будет признаком "скилл собственный или который требуется"
                Возможная реализация:
                ExpandedWrap disabled
                  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)
                    );


                Запрос на получение партнёров, соответствующих всем критериям:
                ExpandedWrap disabled
                  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 -
                  Амигос, вся эта затея, скажем так, разбивается при совсем небольшом усложнении требований по поиску.

                  Пример:

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

                  Я вижу пока один выход - развернуть строки в столбцы (pivot) и по ним строить сложные условия.
                  Для фиксированного количества разворачиваемых столбцов скилов - вроде не сложно, а вот для произвольного - в MySQL я пока не знаю как.
                    Цитата Majestio @
                    а вот для произвольного - в 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 -
                      Akina, вообще я подразумевал набор "1 или 5" для Laravel, а не интервал "от 1 до 5", но с интервалами тоже полезно, это у меня следующая задача.
                      :crazy:

                      Цитата Majestio @
                      2) не делать доп. таблицу, а добавить поле в user2skill, которое будет признаком "скилл собственный или который требуется"
                      Видимо надо столбец `have` таблицы `users` в отдельную таблицу перенести.
                      :scratch:
                      Сообщение отредактировано: RedLight -
                        Цитата RedLight @
                        вообще я подразумевал набор "1 или 5" для Laravel

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

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

                          Добавлено
                          Я повторюсь: решите мой ранее заданный вопрос. А потом в рамках неопределенного заранее набора скилов.
                            Цитата Majestio @
                            Жестоко заданные CASE и WHEN не решают вопрос универсально.

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

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

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


                                Рейтинг@Mail.ru
                                [ Script execution time: 0,0408 ]   [ 15 queries used ]   [ Generated: 6.10.24, 10:19 GMT ]