#5 Практическое задание №5 -- связь "многие ко многим" (много статьей и у каждой несколько авторов)

Primary tabs

vedro-compota's picture
Submitted by vedro-compota on Fri, 02/23/2018 - 15:26

Forums:

В одном из предыдущих заданий, мы добавили возможность создавать учетные записи пользователей в базе данных, также у нас имеется сущность "Статья". Теперь добавим возможность в админке для каждой статьи указать автора причем не обязательно одного, а, возможно, и сразу нескольких.

Таким образом мы научимся работать связью "Многие ко многим".
Для этого:

  1. Пишем запрос, добавляющий таблицу связи -- в ней два "технических" поля -- одно указывает на id статьи а другое на id пользователя (оба внешние ключи). (подумайте, что можно тут сделать первичным ключом)
  2. Далее в форму редактирования статьи добавьте выпадающий список, где в качестве значения каждого пункта используется id пользователя, а в в качестве текста пункта -- логин пользователя, сделайте выбор этого селектбокса множественным (чтобы прижав контрл можно было выбрать сразу нескольких авторов).

Key Words for FKN + antitotal forum (CS VSU):

  • Log in to post comments
  • 53023 reads
JinJim's picture

JinJim

Sat, 02/24/2018 - 15:51

https://github.com/kdn2517/my-first-cms-...

vedro-compota's picture

vedro-compota

Sat, 02/24/2018 - 16:52

CREATE TABLE cms.users_articles (id SMALLINT(10) NOT NULL AUTO_INCREMENT,
user SMALLINT(10) NOT NULL, article SMALLINT(10) NOT NULL, PRIMARY KEY(id))
ENGINE = MyISAM;

замечания:

  • почему взяли именно этот (MyISAM) движок таблицы, почему не InnoDB, например?
  • user SMALLINT(10) NOT NULL,
    article SMALLINT(10) NOT NULL

    -- эти поля лучше называть по смыслу, например:

    user_id SMALLINT(10) NOT NULL, 
  article_id SMALLINT(10) NOT NULL
  • user_id, article_id -- должны быть именно внешними ключами (чтобы субд даже без приложения контролировала допустимость добавляемых туда значений, смотря есть ли такие id в соответствующей таблице).

_____________
матфак вгу и остальная классика =)

JinJim's picture

JinJim

Sun, 02/25/2018 - 05:34

1. На движке MyISAM быстрее работают операции INSERT и SELECT, а они преобладают в этой таблице.
Меняю движок:
ALTER TABLE users_articles engine=InnoDB;
2. Устанавливаю связи:
ALTER TABLE `users_articles` ADD INDEX( `user`);
ALTER TABLE `users` ADD UNIQUE( `id`);
ALTER TABLE `users_articles` ADD INDEX( `article`);
ALTER TABLE `articles` ADD UNIQUE( `id`);
ALTER TABLE users_articles ADD FOREIGN KEY (user) REFERENCES users (id) ON DELETE RESTRICT ON UPDATE RESTRICT ;
ALTER TABLE users_articles ADD FOREIGN KEY (article) REFERENCES articles (id) ON DELETE RESTRICT ON UPDATE RESTRICT ;

vedro-compota's picture

vedro-compota

Sun, 02/25/2018 - 07:11

На движке MyISAM быстрее работают операции INSERT и SELECT

-- и главное, что есть полнотекстовый поиск, но нет транзакций -- что очень важно. По-умолчанию используйте InnoDB -- тем более что там тоже у есть полнотекстовый поиск.

_____________
матфак вгу и остальная классика =)

JinJim's picture

JinJim

Sun, 02/25/2018 - 07:12

ok

vedro-compota's picture

vedro-compota

Tue, 02/27/2018 - 14:34

Просьба разметить весь код в README.md формате маркдаун (в частности это касается кода SQL-запросов) чтобы не был просто текст https://github.com/kdn2517/my-first-cms-...)

_____________
матфак вгу и остальная классика =)

vedro-compota's picture

vedro-compota

Tue, 02/27/2018 - 14:47

готово

JinJim, опишите на русском ваш алгоритм (несколько шагов) извлечения статьи по id вместе с авторами -- вам как минимум нужен массив их имен, но я что-то не пойму как вы его извлекаете, используя таблицу связи.

_____________
матфак вгу и остальная классика =)

JinJim's picture

JinJim

Tue, 02/27/2018 - 15:08

1. В конструктор Article я передаю вторым параметром массив всех связей (по сути всю таблицу, которую извлекаю в getList).
2. В конструкторе обычным перебором массива сравниваю значения id статьи и значения из таблицы связи, если они совпадают - добавляю в массив.

Могу поподробней - со ссылками попробовать объяснить.

vedro-compota's picture

vedro-compota

Tue, 02/27/2018 - 15:14

не надо. понял. Тогда надо переделать необходимо извлекать авторов для каждой статьи используя JOIN (соединение таблиц) (в реальной проекте у тебя могут быть миллионы связей - нельзя вытаскивать всё каждый раз)

_____________
матфак вгу и остальная классика =)

JinJim's picture

JinJim

Wed, 02/28/2018 - 21:01

Ознакомился с оператором JOIN, может быть я чего-то не понимаю, но на мой взгляд в данном конкретном случае его не нужно использовать. Объясню свою логику: для вывода всех статей к

SELECT SQL_CALC_FOUND_ROWS  *, UNIX_TIMESTAMP(publicationDate) 
                AS publicationDate
                FROM articles $clause
                ORDER BY  $order  LIMIT :numRows

добавляем JOIN, получим что-то вроде этого:

SELECT articles.*,  t1.user 
    FROM articles JOIN users_articles AS t1 ON articles.id=t1.article

На сколько я понимаю как результат мы получаем таблицу, где данные из таблицы articles повторяются столько раз, сколько авторов у статьи (а если авторов 10, а статьи огромные не нагрузит ли это сервер?). Не проще ли сделать два запроса? Для вывода всех статей в любом случае нам нужны обе таблицы целиком, а для вывода статей определенного автора у меня стоит условие.

vedro-compota's picture

vedro-compota

Fri, 03/09/2018 - 19:48

SELECT articles.*, t1.user FROM articles JOIN users_articles AS t1 ON articles.id=t1.article

-- что такое t1? Если это таблица связи, то логичнее (и намного понятнее, что важно) было бы назвать её articles_users.

Рекомендую ознакомиться с частью "извлечение данных" вот здесь: http://fkn.ktu10.com/?q=node/9983

_____________
матфак вгу и остальная классика =)

JinJim's picture

JinJim

Sat, 03/10/2018 - 13:42

t1 это таблица связи, я ее так и назвал - users_articles:

...users_articles AS t1...

Да, действительно можно использовать JOIN:

SELECT articles.*,  t1.user
    FROM articles JOIN users_articles AS t1 ON articles.id=t1.article
;

Как результат мы получаем таблицу, где данные из таблицы articles повторяются столько раз, сколько авторов у статьи (а если авторов 10, а статьи огромные это нагрузит сервер). Проще сделать два запроса. Для вывода всех статей в любом случае нам нужны обе таблицы целиком, а для вывода статей определенного автора у меня стоит условие.

В крайнем случае, если у нас действительно миллион связей, а нужно вывести 5 статей, то можно взять сделать первый запрос (который был у нас), забрать там id статей и по ним сделать второй запрос в таблицу связи, потом так же массивом передать в конструктор. В результате получим два небольших компактных запроса вместо одного мега-запроса (данные из которого еще и обрабатывать придется).

vedro-compota's picture

vedro-compota

Mon, 03/12/2018 - 10:20

SELECT articles.*, t1.user

а t1.user -- это id пользователя в таблице связи? лучше называть "как есть", чтобы не было догадок, те.е не user а user_id (при создании схемы таблицы)

_____________
матфак вгу и остальная классика =)

vedro-compota's picture

vedro-compota

Mon, 03/12/2018 - 10:50

Как результат мы получаем таблицу, где данные из таблицы articles повторяются столько раз, сколько авторов у статьи (а если авторов 10, а статьи огромные это нагрузит сервер).

Сервер такой подход не нагрузит особо -- если авторов 10, но вот если это другая ситуация и в табилце связи для данной сущности этих "многих" --миллионы, то да -- дублирование данных приведёт к тому, что ответ СУБД будет попадать в приложение не быстро.
Но есть и другая проблема, запрос:

SELECT articles.*,  t1.user
    FROM articles JOIN users_articles AS t1 ON articles.id=t1.article
;

Не только дублирует извлекаемые данные, но и не получает сразу всех информации о "присоединенных" сущностях, дело в том, что необходимо делать join не с таблицей связи и данной сущностью, а с таблицей связи и таблицей присоединённой сущности, учитывая ограничение (напр. на id данной сушности) -- как это сделано в статье (см. "извлечение данных" для 1 сущности) и также можно глянуть пояснение на видео: https://youtu.be/XKEwmJR4rHk

_____________
матфак вгу и остальная классика =)

JinJim's picture

JinJim

Tue, 03/13/2018 - 12:27

Спасибо за подробное объяснение, а то у меня этот GROUP BY прямо мимо ушей проходил)) Как то так:
Это запрос:

$sql = "SELECT SQL_CALC_FOUND_ROWS a.*, "
                  . "UNIX_TIMESTAMP(a.publicationDate) AS publicationDate,"
                  . "GROUP_CONCAT(users.login SEPARATOR ', ') as users_name "
                  . "FROM articles AS a "
                  . "LEFT JOIN users_articles AS t1 ON a.id=t1.article "
                  . "LEFT JOIN users ON users.id=t1.user $clause "
                  . "GROUP BY a.id ORDER BY :order LIMIT :numRows";

Это на github:
https://github.com/kdn2517/my-first-cms-...
Все работает, а главное это действительно проще, причем как в плане кода так и в плане нагрузки на сервер.

sid's picture

sid

Sat, 02/24/2018 - 21:14

Вопрос нужно ли перестраивать таблицу users, ведь там у нас вместо id, которое должно быть primary key, служит поле login, которое unique index.

vedro-compota's picture

vedro-compota

Sun, 02/25/2018 - 06:58

нужно ли перестраивать таблицу users, ведь там у нас вместо id, которое должно быть primary key, служит поле login, которое unique index.

всё-таки грамотнее перестроить, хотя конечно, можно и на текстовый ключ ссылаться текстовым значением же -- но это занимает больше памяти.

_____________
матфак вгу и остальная классика =)

sid's picture

sid

Tue, 02/27/2018 - 14:12

С таблицей более менее понятно, теперь встает вопрос как это обрабатывается на уровне классов, ведь теперь у нас новая таблица, которую нужно обработать со стороны двух классов user и articles?

vedro-compota's picture

vedro-compota

Tue, 02/27/2018 - 14:29

опрос как это обрабатывается на уровне классов, ведь теперь у нас новая таблица, которую нужно обработать со стороны двух классов user и articles

Если кратко:

  • обрабатывать отдельно (создавать класс некоторой сущности) для такой таблицы не надо -- не зря она называется "таблицей связи", это просто "техническая" таблица
  • тем не менее нам надо модифицировать все CRUD запросы, относящиеся к сущности "Статья", так чтобы можно было вместе с ней работать и с авторами, которые к этой статье относятся. Например для извлечения авторов месте со статьёй по её id (метод Article::getById()) -- можно было бы использовать LEFT JOIN между таблицей связи и таблицей пользователей.

_____________
матфак вгу и остальная классика =)

sid's picture

sid

Tue, 02/27/2018 - 16:49

И все же я пока не совсем понимаю, как это все связать, откуда попадают данные в связную таблицу и как их туда записать?

vedro-compota's picture

vedro-compota

Tue, 02/27/2018 - 19:12

откуда попадают данные в связную таблицу и как их туда записать?

возможный алгоритм

  • методом POST скрипту-оброботчику приходит от формы id статьи (скрытое поле) + массив id авторов (множественный селекбокс о котором говорится в задании)
  • вы обновляете собственные поля статьи (как и раньше),
  • удаляете все старые (которые были до того момента, как пришли данные от формы) связи с авторами,
  • а затем (непосредственно сразу за предыдущим) формируете insert-запрос в таблицу связи -- вставляете туда пары id_статьи|id_автора

- при данном подходе обновление редактируемой сущности обеспечивается тремя SQL запросами:

  1. update собственных полей (напр. заголовка -- ну и вообще всего что поменялось в форме)
  2. удаление старых связей (у нас тут их не сильно много, поэтому проще удалить всё)
  3. insert в таблицу связи, причем надо сделать именно один insert дабы не повышать нагрузку).

-- для того, чтобы обновление гарантированно прошло целиком (или не прошло вообще -- лишь бы не частично) -- оберните в транзакцию, код работающий с базой (обеспечивающий все три запроса)

_____________
матфак вгу и остальная классика =)

sid's picture

sid

Wed, 02/28/2018 - 20:27

Прошу привести конкретный пример, т.к для меня это пока не просто дается

sid's picture

sid

Wed, 03/14/2018 - 14:59

Прошу посмотреть пример

vedro-compota's picture

vedro-compota

Fri, 03/09/2018 - 20:00

Прошу привести конкретный пример, т.к для меня это пока не просто дается

sid, смотрите:

  • алгоритм сохранения я описал выше
  • откуда попадают данные в связную таблицу и как их туда записать?

    -- используя этот алгоритм вы должны просто сделать вставку id сущностей в таблицу связи после того, как форма сохранения статьи пришла на сервер, для этого вам надо выполнить сначала INSERT для полей статьи, а потом INSERT в таблицу связи -- и то и то присутствует например в коде разворота примера

Ознакомьтесь с примером и выполните задания: http://fkn.ktu10.com/?q=comment/2282#com...
-- если там все будет понятно, то вернёмся к этому обсуждению.

_____________
матфак вгу и остальная классика =)

Pavel1989's picture

Pavel1989

Sat, 09/07/2019 - 13:47

Выполнено.

Коммит

dimmkan's picture

dimmkan

Sat, 11/02/2019 - 18:44

Ссылка на коммит

NewDeveloper

Thu, 02/03/2022 - 15:00

Изменение 5