У меня есть твердый орешек, чтобы взломать, соединяя 3 таблицы. У меня есть newsletter_items, newsletter_fields и newsletter_mailgroups, к которым я хочу присоединиться, чтобы получить список информационных бюллетеней.
В информационных бюллетенях содержатся поля:
letter_id, letter_date, receivers, template, status That can look like 1, 1234567899, 1,2 (comma separated), standard.html, 1 newsletter_fields содержит поля:
field_uid, field_name, field_content, field_letter_uid That can look like 1, letter_headline, A great headline, 1
где field_letter_uid – это информационный бюллетень, к которому относится поле.
и newsletter_mailgroups содержит поля:
mailgroup_id, mailgroup_name, number_of_members That can look like 1, Group1, 233 2, Group2, 124 3, Group3, 54
Я хочу объединить эти 3 таблицы, чтобы я мог получить список всего информационного бюллетеня следующим образом:
Letter date | Letter headline | Receivers | Status 2008-01-01 12:00:00 | A great headline | Group1, Group 2 | 1
Короче говоря, я хочу, чтобы мой SQL-запрос присоединился к трем таблицам, и в этом процессе выберите получателей из таблицы mailgroup и покажите их запятыми, как Group1, Group 2
Это то, что я получил сейчас
SELECT A.*, B.* FROM newsletter_items A, newsletter_fields B, WHERE B.field_letter_uid = A.letter_id AND field_name = 'letter_headline' AND A.template = '". $template ."';
Но я не могу понять, как получить в них почтовые группы.
Я рекомендую вам сделать ваши соединения явными.
Это облегчает отладку вашего запроса и изменение внутреннего с помощью левого соединения.
Нет абсолютно никакой причины использовать синтаксис неявного соединения SQL'89.
SELECT ni.* , nf.* , group_concat(nm.mailgroup_name) as mailgroups FROM newsletter_items ni INNER JOIN newsletter_fields nf ON (nf.field_letter_uid = ni.letter_id) INNER JOIN newsletter_mailgroups nm ON (find_in_set(nm.mailgroup_id, ni.receivers)) WHERE nf.field_name = 'letter_headline' ni.template = '". $template ."' GROUP BY ni.letter_id;
Что касается дизайна вашей базы данных.
Я рекомендую вам нормализовать вашу базу данных, это означает, что вы перемещаете поля, разделенные запятыми, в другую таблицу.
Итак, вы делаете приемники таблиц
Receivers ---------- id integer auto_increment primary key letter_id integer not null foreign key references newsletter_items(letter_id) value integer not null
Затем вы удаляете полевой приемник из таблицы newsletter_items
Затем ваш запрос изменяется на:
SELECT ni.* , group_concat(r.value) as receivers , nf.* , group_concat(nm.mailgroup_name) as mailgroups FROM newsletter_items ni INNER JOIN newsletter_fields nf ON (nf.field_letter_uid = ni.letter_id) INNER JOIN newsletter_mailgroups nm ON (find_in_set(nm.mailgroup_id, ni.receivers)) LEFT JOIN receiver r ON (r.letter_id = ni.letter_id) WHERE nf.field_name = 'letter_headline' ni.template = '". $template ."' GROUP BY ni.letter_id;
Это изменение также должно значительно ускорить ваш запрос.
Если это разрешено, почему бы вам не создать новую таблицу, называемую newsletter_item_receivers, где вы могли бы хранить поля letter_id, receiver_id? Имея значения, разделенные запятыми в таком поле, как правило, означает, что вам не хватает таблицы 🙂
Редактировать:
Используя CSV, вы делаете свою жизнь несчастной, когда хотите получить ответ, чтобы «дать мне все информационные бюллетени, которые получает receiver_id = 5» 🙂
Вот хороший ответ на аналогичный вопрос о SO: значения, разделенные запятыми, в поле базы данных
Edit2:
Если я правильно понимаю ваши отношения с таблицами, это будет примерно так:
SELECT a.letter_date, b.receiver_id, a.status FROM newsletter_items_receivers b LEFT OUTER JOIN newsletter_items a ON (a.letter_id = b.letter_id) LEFT OUTER JOIN newsletter_mailgroups m ON (m.mailgroup_id = b.receiver_id)
ЗАМЕТКА! Этот запрос НЕ вернет информационный бюллетень, если нет получателей этого бюллетеня. Если вам нужна эта функциональность, вы можете попробовать что-то вроде этого:
SELECT x.letter_date, y.mailgroup_name, x.status FROM ( SELECT a.letter_date, b.receiver_id, a.status FROM newsletter_items a LEFT OUTER JOIN newsletter_items_rec b ON (b.letter_id = a.letter_id)) x LEFT OUTER JOIN newsletter_mailgroups y ON (y.mailgroup_id = x.receiver_id)
У меня нет доступа к SQL прямо сейчас, поэтому я мог бы сделать некоторые синтаксические ошибки (надеюсь, не логические :)).
Что касается того, почему мы это делаем, как отметил @Konerak, вам было бы полезно ознакомиться с нормализацией базы данных и почему это важно.
Вы можете начать с этой статьи с about.com, просто взглянув на нее, кажется, что прочитал это нормально. http://databases.about.com/od/specificproducts/a/normalization.htm
Кроме того, было бы хорошо, если бы имена полей были одинаковыми для нескольких таблиц. Например, у вас есть letter_id в newsletter_items, но у вас есть field_letter_uid в newsletter_fields. Просто мысль 🙂
Попробуй использовать
SELECT A.*, B.*, group_concat(C.mailgroup_name SEPARATOR ',') FROM newsletter_items A, newsletter_fields B, newsletter_mailgroups C WHERE B.field_letter_uid = A.letter_id AND field_name = 'letter_headline' AND A.template = '". $template ."' and find_in_set(c.mailgroup_id, A.receivers) group by A.letter_id;