What approach can you recommend for data grouping?

[rastym]

For example, let's consider the following structure. If we perform a JOIN on these tables, we'll end up with two records for one user.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);
INSERT INTO users (username, email) VALUES
    ('user1', 'user1@example.com');
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);
INSERT INTO posts (title, content, user_id) VALUES
    ('Post 1', 'Content of post 1', 1),
    ('Post 2', 'Content of post 2', 1),

How can we process the data to obtain such a structure?:
User { id, username, email, posts: [{ title: 'Post 1', content: 'Content of post 1', user_id: 1 }] }
Of course, we can write parsers each time, use helper functions from lodash, but is there a more fundamental approach? Thank you!

[wsporto]

I have been exploring this feature in TypeSQL (a similar library but for mysql). The approach I have used is to annotate the queries with -- @nested as below:

-- @nested
 SELECT
  *
FROM users u
INNER JOIN posts p on p.user_id = u.id
WHERE u.id = :id

Then TypeSQL will generate the function selectUserPostsNested:

const user = await selectUserPostsNested(conn, {id: 1});

The result type will have the structure:

{ id: 1, username: 'user1', email: 'user1@example.com', posts: [{ title: 'Post 1', content: 'Content of post 1', user_id: 1 }] }

[lorefnon]

You can either use:

1. Lateral joins to fetch associated data and build hierarchy in the same (complex) query. Look at the queries generated by libraries like Zapatos or drizzle.
2. You can make multiple simple queries (one for each level of association) and combine the fetched collections into a hierarchy using collection-joiner - a library I maintain to handle this in a type-safe manner.

[rastym]

@wsporto , @lorefnon thanks for your response!