Выбор из нескольких таблиц

Содержание

Вопрос или проблема
Ответ или решение
Как выбрать данные из нескольких таблиц в SQLite
Структура таблиц
Необходимый SQL-запрос
Упрощение результатов с использованием group_concat
Корректная структура таблиц
Заключение

Вопрос или проблема

У меня есть три таблицы SQLite, созданные с помощью следующего скрипта

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS entries(
    timestamp TEXT,
    title TEXT,
    body TEXT
);

CREATE TABLE IF NOT EXISTS tags(
    tag TEXT
);

CREATE TABLE IF NOT EXISTS entry_tags(
    entry_id INTEGER,
    tag_id INTEGER,
    FOREIGN KEY (entry_id) REFERENCES entries(rowid),
    FOREIGN KEY (tag_id) REFERENCES tags(rowid)
);

Мой вопрос прост, но я не могу понять, как на него ответить. Как использовать SQL, чтобы получить временную метку, заголовок, тело записи и все связанные теги? Я предполагаю, что это включает JOIN где-то в процессе, но, несмотря на поиски, я не могу найти понятный ответ.

Да, вам нужно сделать JOIN, используя вашу таблицу связей entry_tags. Что-то вроде этого (обратите внимание, что я использую инструмент командной строки Sqlite, а не модуль Python, но SQL команды одинаковы):

sqlite> select e.timestamp, e.title, e.body, t.tag 
   ...> from entries as e 
   ...>      join entry_tags et on e.rowid = et.entry_id
   ...>      join tags as t on et.tag_id = t.rowid;
┌────────────┬───────────┬────────────────┬───────┐
│ timestamp  │   title   │      body      │  tag  │
├────────────┼───────────┼────────────────┼───────┤
│ 2024-11-01 │ Article A │ blah blah blah │ alpha │
│ 2024-11-01 │ Article A │ blah blah blah │ beta  │
└────────────┴───────────┴────────────────┴───────┘

Однако обратите внимание, что вы получите дублирующиеся строки для статьи, если на ней есть более одного тега. В этом случае вы можете использовать функцию group_concat (см. Агрегация строк в sqlite), чтобы агрегировать теги (по умолчанию разделенные запятой):

sqlite> select e.timestamp, e.title, e.body, group_concat(t.tag) as tags 
   ...> from entries as e 
   ...>      join entry_tags et on e.rowid = et.entry_id
   ...>      join tags as t on et.tag_id = t.rowid;
┌────────────┬───────────┬────────────────┬────────────┐
│ timestamp  │   title   │      body      │    tags    │
├────────────┼───────────┼────────────────┼────────────┤
│ 2024-11-01 │ Article A │ blah blah blah │ alpha,beta │
└────────────┴───────────┴────────────────┴────────────┘

К слову, пожалуйста, обратите внимание, что Sqlite обычно не позволяет использовать неявный столбец rowid в качестве первичного ключа (и, таким образом, ссылаться на него в качестве внешнего ключа), если вы не укажете его явно в схеме таблицы (см. https://www.sqlite.org/rowidtable.html). Например, вы должны определить entries и tags следующим образом:

CREATE TABLE entries(
    rowid INTEGER PRIMARY KEY,
    timestamp TEXT,
    title TEXT,
    body TEXT
);
CREATE TABLE tags(
    rowid INTEGER PRIMARY KEY,
    tag TEXT
);

Ответ или решение

Как выбрать данные из нескольких таблиц в SQLite

В данной статье мы разберём, как с помощью SQL-запросов извлекать данные из нескольких таблиц в базе данных SQLite. Предположим, у нас есть три таблицы: entries, tags и entry_tags. Эти таблицы хранят информацию о записях, тегах и связывающей таблице между записями и тегами соответственно. Мы используем основной подход — JOIN, чтобы комбинировать данные из различных таблиц.

Структура таблиц

Таблицы созданы с использованием следующего SQL-скрипта:

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS entries(
    timestamp TEXT,
    title TEXT,
    body TEXT
);

CREATE TABLE IF NOT EXISTS tags(
    tag TEXT
);

CREATE TABLE IF NOT EXISTS entry_tags(
    entry_id INTEGER,
    tag_id INTEGER,
    FOREIGN KEY (entry_id) REFERENCES entries(rowid),
    FOREIGN KEY (tag_id) REFERENCES tags(rowid)
);

Обратите внимание, что entry_tags является соединительной таблицей, которая связывает entry_id из таблицы entries с tag_id из таблицы tags.

Необходимый SQL-запрос

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

SELECT e.timestamp, e.title, e.body, t.tag 
FROM entries AS e 
JOIN entry_tags AS et ON e.rowid = et.entry_id 
JOIN tags AS t ON et.tag_id = t.rowid;

В данном запросе:

e, et, и t — это псевдонимы для таблиц entries, entry_tags и tags, соответственно.
Мы связываем таблицы, используя соответствующие ключи для получения нужных данных.

Упрощение результатов с использованием group_concat

Следует иметь в виду, что если у записи несколько тегов, запрос будет создавать дубликаты. Решение этой проблемы заключается в использовании функции group_concat, которая позволяет объединять теги в одну строку, разделённую запятой:

SELECT e.timestamp, e.title, e.body, GROUP_CONCAT(t.tag) AS tags 
FROM entries AS e 
JOIN entry_tags AS et ON e.rowid = et.entry_id 
JOIN tags AS t ON et.tag_id = t.rowid
GROUP BY e.rowid;

Здесь мы добавили GROUP BY e.rowid, что позволяет сгруппировать все записи по одной и той же записи, а все связанные теги будут объединены в один столбец.

Корректная структура таблиц

Важно отметить, что SQLite требует явного определения первичного ключа, если вы хотите использовать rowid в качестве внешнего ключа. Чтобы ваш код работал без ошибок, создайте таблицы следующим образом:

CREATE TABLE entries(
    rowid INTEGER PRIMARY KEY,
    timestamp TEXT,
    title TEXT,
    body TEXT
);

CREATE TABLE tags(
    rowid INTEGER PRIMARY KEY,
    tag TEXT
);

Заключение

С использованием SQL вы можете эффективно управлять данными в SQLite и извлекать сложные наборы данных через JOIN. С помощью приведённых выше подходов вы можете не только получать информацию о записях и связанных тегах, но и управлять выводом данных, предотвращая дублирование. Это важные навыки для профессионалов в области IT, работающих с базами данных. Используйте данные запросы как основу для дальнейшего изучения и расширения функционала ваших баз данных.