Запрос в базу с JOIN с нескольких таблиц?

Question

[direx]

Добрый день.
Интересует такой вопрос, а точней как составит правильно запрос mysql.
Есть несколько таблиц, вот их структура:

USER:

id | login | 
1 | Николай |
2 | Дмитрий |

TASK:

id | author_user_id | performer_user_id | task_message | status
1 | 1 | 2 | Задача 1 | 2

TASK_STATUS:

id | status
1 | Ожидает

Как составить составить правильно запрос так что бы mysql вернул мне:

id : 1
author_user_id : {login: Николай}
performer_user_id: {login: Дмитрий}
task_message: Задача 1
status: Ожидает

Answer 1

[Rsa97]

SELECT `t`.`id`, `au`.`login`, `pu`.`login`, `t`.`task_message`, `ts`.`status`
    FROM `TASK` AS `t`
    LEFT JOIN `USER` AS `au` ON `au`.`id` = `t`.`author_user_id`
    LEFT JOIN `USER` AS `pu` ON `pu`.`id` = `t`.`performer_user_id`
    LEFT JOIN `TASK_STATUS AS `ts` ON `ts`.`id` = `t`.`status`
    WHERE ...

Answer 2

[Евгений]

3 запроса делайте, это будет лучше.
И это не потому что я не знаю как джоин работает, а потому что джоины зло.

Comments

[direx]

Да, но три запроса это на один вывод, а если их сотня?

[Евгений]

ponich: Все равно будет 3 запроса.
1 - достали задачи ( select * from task)
2- достали юзеров (select * from user where id IN(1,2,3,4) )
3 - достали статусы (select * from status)
ну и привинтили данные

[Евгений]

ponich: Вот ответ на вопрос, но это очень плохое решение
SELECT * from task
LEFT JOIN user author_user ON author_user.id = task.author_user_id
LEFT JOIN user performer_user ON performer_user.id = task.performer_user_id
LEFT JOIN task_status ON task_status.id = task.status

[direx]

Nc_Soft: Спасибо. Сейчас попробую сделать foreach это все. Скажем так, запрос в запросе =) Посмотрим что быстрей. Просто опыта в этом не было.

[Евгений]

ponich: всего должно быть 3 запроса.
Сначала все задачи достаем, потом в цикле собираем все нужные ид юзеров, потом тащим юзеров за 1 запрос.

[direx]

Nc_Soft: Получилось так:
$task = array();

$result['task'] = $this->db->get('task', $limit, $offset)->result_array();

$i = 0;
foreach($result['task'] as $list){
$task[$i]['task_id'] = $list['id'];
$task[$i]['create_data'] = $list['create_data'];
$task[$i]['message'] = $list['message'];

// автор задачи
$this->db->select('user_id, firstName, lastName');
$this->db->where('user_id', $list['author_user_id']);
$result['author_user'] = (array) $this->db->get('user_profile')->row();

$task[$i]['author_user'] = $result['author_user'];

// кому адрессована задачи
$this->db->select('user_id, firstName, lastName');
$this->db->where('user_id', $list['performer_user_id']);
$result['performer_user'] = (array) $this->db->get('user_profile')->row();

$task[$i]['performer_user'] = $result['performer_user'];

// статус задачи
$this->db->select('title');
$this->db->where('id', $list['status']);
$result['status'] = (array) $this->db->get('task_status')->row();

$task[$i]['status'] = $result['status']['title'];

$i++;
}

Работает! Спасибо Вам

[ylebedev]

Что то мне подсказывает, что вы тут ад склоняете.

Скачайте DBForge.

[Евгений]

ponich: я же говорил, что должно быть всего 3 запроса, а тут селекты по юзерам внутри цикла...

[direx]

ylebedev: Подробней пожалуйста?

[direx]

Nc_Soft: Ну так а если мне нужно 100 записей, то это уже не как не три запроса?

[Евгений]

ponich: говорю же все равно 3 запроса
<?php
// получить все задачи (ПЕРВЫЙ ЗАПРОС)
$tasks = $this->db->get('task', $limit, $offset)->result_array();

// сюда сложим юзеров
$users = [];

// сюда сложим статусы
$statuses = [];

foreach ($tasks as $task) {
// автор задачи
$users[$task['author_user_id']] = null;

// кому адрессована задачи
$users[$task['performer_user_id']] = null;

// статус задачи
$statuses[$task['status']] = null;
}

if ($users) {
// получить всех нужных юзеров (ВТОРОЙ ЗАПРОС)
// $rows = select * from user WHERE id IN( implode(',', array_keys($users)) )
foreach ($rows as $row) {
$users[$row['id']] = $row;
}
}

if ($statuses) {
// получить все нужные статусы (ТРЕТИЙ ЗАПРОС)
// $rows = select * from task_status WHERE id IN( implode(',', array_keys(statuses)) )
foreach ($rows as $row) {
$statuses[$row['id']] = $row;
}
}

foreach ($tasks as &$task) {
$task['author_user'] = $users[$task['author_user_id']];
$task['performer_user'] = $users[$task['performer_user_id']];
$task['status'] = $statuses[$task['status']];
}

[Dremkin]

Nc_Soft: вы пишите ересь. Умрите сейчас, не допускайте размножения. Автору: только join, никаких циклов. Если миллион строчек - используйте индексы. Всё.

[Rsa97]

Угу. Теперь представьте обычную производственную ситуацию. Имеем 5000 пользователей форума, надо получить 10 свежих топиков с их топикстартерами. Вы всё ещё предпочитаете вместо JOIN каждый раз считывать всех пользователей?

[Евгений]

Dremkin: Ну сразу видно человек с хайлоад не работал никогда и про масштабируемость вообще не слышал, зато предлагает всем умирать.

[Dremkin]

Nc_Soft: вы в хайлоаде с помощью циклов данные связываете? ) с таким подходом любой проект будет хайлоадом ) правда, идите поучитесь, хотя-бы на курсах начальных, не вводите людей в заблуждение.

[Евгений]

Dremkin: Да, прикинь, а еще кеш использую и, о боже, несколько баз данных (горизонтальное масштабирование). И джоины там ну никак ни але.
А на курсах начальников вы только попец лизать научитесь без реальной практики.

[Dremkin]

Nc_Soft: Горизонтальное масштабирование.. ого. Вы крутой. Посыпаю голову пеплом.

[Rsa97]

Nc_Soft: А как обеспечиваете атомарность операции? Что произойдёт, если между запросом списка пользователей и списка задач (пока статусы выбираются из базы) будут добавлены новый пользователь и новая задача?

[Евгений]

Rsa97: Ну сначала же задачи выбираются, а потом связанные с ними сущности. А так да, атомарность, это конечно, тот еще геморой, ограничивается выбором фактора для шардинга.
Ну тут речь не об этом, а о том, что правильно разные сущности доставать отдельными запросами.

[-- --]

Хайлоад - это сколько в терабайтах и пользователях?

Answer 3

[ylebedev]

Если не сложно, скиньте SQL файл (точный экспорт таблиц).
Лень набивать в свою базу руками.

DBForge в граф. виде позволяет проектировать базу данных выдавая правильный вариант запроса.
Включая не позволяет запутаться в JOIN

SELECT
task.id,
task.message,
task_status.title,
user_profile.firstName,
user_profile.lastName,
user_profile_1.firstName AS performer_user_id
FROM task
LEFT JOIN task_status
ON task.status = task_status.id
LEFT JOIN user_profile
ON task.author_user_id = user_profile.user_id
LEFT JOIN user_profile user_profile_1
ON task.performer_user_id = user_profile_1.user_id

1
admin
adminovich
pertovich
О море мия гаднжубас
Ожидает

Добавил пару стобцов

Для SQL лучше использовать 1 длинный запрос, чем циклы 1000 мелких.

Я уже от циклов пострадал по старой школе php. В результате php гнул SQL по 50 000 запросов в сек.
потому что продукт вырос.

Comments

[direx]

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


CREATE TABLE IF NOT EXISTS `task` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `project_id` int(9) NOT NULL,
  `author_user_id` int(9) NOT NULL,
  `performer_user_id` int(9) NOT NULL,
  `create_data` date NOT NULL,
  `status` int(9) NOT NULL,
  `message` text NOT NULL,
  `description` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;


CREATE TABLE IF NOT EXISTS `task_status` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;


CREATE TABLE IF NOT EXISTS `user` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `password` varchar(255) NOT NULL COMMENT 'md5(email + md5(password))',
  `email` varchar(255) NOT NULL,
  `level` int(11) NOT NULL COMMENT 'Уровень доступа',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Пользователи системы' AUTO_INCREMENT=7 ;

CREATE TABLE IF NOT EXISTS `user_profile` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `user_id` int(9) NOT NULL,
  `firstName` varchar(255) NOT NULL,
  `lastName` varchar(255) NOT NULL,
  `phone` varchar(255) NOT NULL,
  `adress` varchar(255) NOT NULL,
  `map` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;