Связи таблиц

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

Примеры используют базу SQLite в памяти и здесь не запускаются — выполните их у себя.

Внешний ключ

Связь хранится в столбце с идентификатором строки другой таблицы. У статьи это user_id — идентификатор автора:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');
$pdo->exec('CREATE TABLE posts (id INTEGER PRIMARY KEY, user_id INTEGER, title TEXT)');
$pdo->exec("INSERT INTO users (name) VALUES ('Анна'), ('Олег')");
$pdo->exec("INSERT INTO posts (user_id, title) VALUES (1, 'Первый пост'), (1, 'Второй пост'), (2, 'Заметка')");
 
$statement = $pdo->prepare('SELECT title FROM posts WHERE user_id = ?');
$statement->execute([1]);
 
echo implode(', ', $statement->fetchAll(PDO::FETCH_COLUMN)); // Первый пост, Второй пост

Имя автора в таблице статей не повторяется: оно хранится один раз, в таблице пользователей.

JOIN

JOIN соединяет строки двух таблиц по условию и возвращает их одной строкой:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');
$pdo->exec('CREATE TABLE posts (id INTEGER PRIMARY KEY, user_id INTEGER, title TEXT)');
$pdo->exec("INSERT INTO users (name) VALUES ('Анна'), ('Олег')");
$pdo->exec("INSERT INTO posts (user_id, title) VALUES (1, 'Первый пост'), (2, 'Заметка')");
 
$rows = $pdo->query('
    SELECT posts.title, users.name
    FROM posts
    JOIN users ON users.id = posts.user_id
    ORDER BY posts.id
')->fetchAll(PDO::FETCH_ASSOC);
 
foreach ($rows as $row) {
    echo $row['title'], ' — ', $row['name'], "\n";
}

Выведется «Первый пост — Анна» и «Заметка — Олег».

LEFT JOIN

Обычный JOIN отбрасывает строки без пары. LEFT JOIN оставляет все строки левой таблицы, а недостающие значения заполняет NULL:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');
$pdo->exec('CREATE TABLE posts (id INTEGER PRIMARY KEY, user_id INTEGER, title TEXT)');
$pdo->exec("INSERT INTO users (name) VALUES ('Анна'), ('Олег')");
$pdo->exec("INSERT INTO posts (user_id, title) VALUES (1, 'Первый пост')");
 
$rows = $pdo->query('
    SELECT users.name, posts.title
    FROM users
    LEFT JOIN posts ON posts.user_id = users.id
    ORDER BY users.id
')->fetchAll(PDO::FETCH_ASSOC);
 
foreach ($rows as $row) {
    echo $row['name'], ': ', $row['title'] ?? 'нет статей', "\n";
}

Олег попал в результат, хотя статей у него нет.

Подсчёт с GROUP BY

GROUP BY собирает строки в группы, а COUNT() считает строки в каждой:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');
$pdo->exec('CREATE TABLE posts (id INTEGER PRIMARY KEY, user_id INTEGER, title TEXT)');
$pdo->exec("INSERT INTO users (name) VALUES ('Анна'), ('Олег')");
$pdo->exec("INSERT INTO posts (user_id, title) VALUES (1, 'A'), (1, 'B'), (2, 'C')");
 
$rows = $pdo->query('
    SELECT users.name, COUNT(posts.id) AS total
    FROM users
    LEFT JOIN posts ON posts.user_id = users.id
    GROUP BY users.id
    ORDER BY users.id
')->fetchAll(PDO::FETCH_KEY_PAIR);
 
echo json_encode($rows, JSON_UNESCAPED_UNICODE); // {"Анна":2,"Олег":1}

Многие ко многим

У статьи много тегов, а у тега много статей. Такую связь хранят в третьей таблице с двумя столбцами: post_id и tag_id. Каждая её строка — одна пара «статья и тег».

Частая ошибка

Запрос в цикле — самая частая причина медленных страниц. Для ста статей получится сто один запрос:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');
$pdo->exec('CREATE TABLE posts (id INTEGER PRIMARY KEY, user_id INTEGER, title TEXT)');
$pdo->exec("INSERT INTO users (name) VALUES ('Анна')");
$pdo->exec("INSERT INTO posts (user_id, title) VALUES (1, 'A'), (1, 'B')");
 
// Плохо: внутри цикла по статьям отдельный запрос автора для каждой статьи.
// Хорошо: один запрос с JOIN сразу возвращает статьи вместе с авторами.
$rows = $pdo->query('SELECT posts.title, users.name FROM posts JOIN users ON users.id = posts.user_id')->fetchAll();
 
echo count($rows); // 2

Спасибо, что читаете!