I have three SQLite tables, created with the following script
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)
);
My question is simple, but I cannot work out how to answer it. How do I use SQL to get an entry's timestamp, title, body and all associated tags? I'm assuming it involves a JOIN somewhere in the process, but despite searching, I cannot find an answer that I can understand.
Yes, you want to do a JOIN, using your entry_tags
junction table. Something like this (note I am just using the Sqlite command line tool, not the Python module, but the SQL commands are the same):
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 │
└────────────┴───────────┴────────────────┴───────┘
However, note that you will get duplicate rows for an article if there is more than one tag on it. In this case, you can use the group_concat
function (see String Aggregation in sqlite) to aggregate the tags (separated by a comma by default):
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 │
└────────────┴───────────┴────────────────┴────────────┘
As an aside, please note that Sqlite will not generally let you use the implicit rowid
column as a primary key (and thus reference as a foreign key) unless you explicitly put it in the table schema (see https://www.sqlite.org/rowidtable.html). For example, you should define entries
and tags
as follows:
CREATE TABLE entries(
rowid INTEGER PRIMARY KEY,
timestamp TEXT,
title TEXT,
body TEXT
);
CREATE TABLE tags(
rowid INTEGER PRIMARY KEY,
tag TEXT
);