Node Express和SQLite3:将相关的多对多数据作为一个对象返回

Question

我有以下表格：

movies     genres     genres_movies
------     ------     -------------
id         id         id
title      name       genre_id
movie_id

使用SQLite3和Node Express，我如何创建一个(或多个(查询来返回以下假设的电影对象？

{
"id": 1,
"title": "Catapult: The Movie",
"genres": [
{
"id": 1,
"name" "documentary"
},
{
"id": 2,
"name": "comedy"
}
]
}

谢谢！

Answer 1

假设您的Sqlite版本启用了JSON1扩展，则类似于：

PRAGMA foreign_keys = on;
CREATE TABLE movies(id INTEGER PRIMARY KEY, title TEXT);
CREATE TABLE genres(id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE genres_movies(id INTEGER PRIMARY KEY
, movie_id INTEGER REFERENCES movies(id)
, genre_id INTEGER REFERENCES genres(id)
);
CREATE INDEX gm_idx_movie_id ON genres_movies(movie_id);
INSERT INTO movies(id, title) VALUES (1, 'Catapult: The Movie')
, (2, 'Deadpool')
, (3, 'Dune');
INSERT INTO genres(id, name) VALUES (1, 'documentary'), (2, 'comedy');
INSERT INTO genres_movies(movie_id, genre_id) VALUES (1, 1), (1, 2), (2, 2);
SELECT json_object('id', m.id
, 'title', m.title
, 'genres',
CASE WHEN g.id ISNULL THEN json_array()
ELSE json_group_array(json_object('id', g.id
, 'name', g.name))
END)
AS movie
FROM movies AS m
LEFT JOIN genres_movies AS gm ON m.id = gm.movie_id
LEFT JOIN genres AS g ON g.id = gm.genre_id
GROUP BY m.id
ORDER BY m.id;

产生

movie                                                                                                  
--------------------------------------------------------------------------------------------------------
{"id":1,"title":"Catapult: The Movie","genres":[{"id":1,"name":"documentary"},{"id":2,"name":"comedy"}]}
{"id":2,"title":"Deadpool","genres":[{"id":2,"name":"comedy"}]}
{"id":3,"title":"Dune","genres":[]}