SQLize | PHPize | SQLtest

-- Hint: use Ctrl+Enter for SQL autocomplete CREATE TABLE portfolio ( id Int NOT NULL, name VarChar(255), email VarChar(255), PRIMARY KEY (id) ); CREATE TABLE photos ( id Int NOT NULL, portfolio_id Int NOT NULL, src VarChar(255), src_build VarChar(255), PRIMARY KEY (id), FOREIGN KEY (portfolio_id) REFERENCES portfolio(id) ); INSERT INTO portfolio VALUES (1, 'NY', 'user_1@test.com'), (2, 'London', 'user_2@test.com'), (3, 'Moscow', 'user_3@test.com'), (4, 'Kiev', 'user_4@test.com'), (5, 'Rome', 'user_5@test.com'), (6, 'Paris', 'user_6@test.com'); INSERT INTO photos VALUES (1, 1, 'Foo', 'complete'), (2, 2, 'Bar', 'complete'), (3, 3, 'FooBar', 'cancelled'), (4, 3, 'BarFoo', 'complete'), (5, 4, 'New', 'complete'), (6, 5, 'Item', 'complete'), (7, 6, 'Foo', 'complete'), (8, 4, 'Bar', 'complete'), (9, 3, 'FooBar', 'cancelled'), (10, 2,'BarFoo', 'complete'), (11, 3,'New', 'complete'), (12, 4, 'Item', 'complete'), (13, 5, 'Item', 'complete'), (14, 1, 'Item', 'complete'), (15, 2, 'Item', 'complete'), (16, 1, 'Item', 'complete'); select portfolio.id, portfolio.name, array_agg(json_build_object('src', photos.src, 'src_build', photos.src_build)) from portfolio left join photos on portfolio.id = photos.portfolio_id group by portfolio.id, portfolio.name ;