メディアアートのためのバックエンド構築入門（Chap.04 リレーション編）

-- studentsテーブルとデータを作成する
create table students(id integer primary key autoincrement, name text, age integer, university_id integer);
insert into students(name, age, university_id) values('太郎', 22, 1);
insert into students(name, age, university_id) values('花子', 23, 2);
insert into students(name, age, university_id) values('次郎', 20, 1);
-- universitiesテーブルとデータを作成する
create table universities(id integer primary key autoincrement, name text, address text);
insert into universities(name, address) values('X大学', 'A県a市1-1-1');
insert into universities(name, address) values('Y大学', 'B府a市1-1-1');
insert into universities(name, address) values('Z大学', 'C都a区1-1-1');
-- テーブルを結合して取得する
select students.id, students.name, universities.name
from students
inner join universities on students.university_id = universities.id;

const query = `
select lines.id as 'line_id', points.id as 'point_id', points.x as 'x', points.y as 'y'
from lines 
inner join points on lines.id = points.line_id`;
db.all(query, (err, rows) => {
    if (err) {
        throw err
    }
    const lineIds = Array.from(new Set(rows.map(row => row.line_id)))
    const lines = lineIds.map(lineId => {
        const points = rows.filter(row => row.line_id === lineId)
            .map(row => {
                return {
                    id: row.point_id,
                    x: row.x,
                    y: row.y,
                }
            })
        return {
            id: lineId,
            points: points,
        }
    })
    callback(lines)
})

// *** Formatting with SQLite **
const query = `
select json_object(
    'id', lines.id,
    'points', json_group_array(
        json_object('id', points.id, 'x', points.x, 'y', points.y)
    )
) line 
from lines 
inner join points on lines.id = points.line_id 
group by lines.id`;
db.all(query, (err, rows) => {
    const lines = rows.map(row => JSON.parse(row.line))
    callback(lines)
})