https://supabase.com/ logo
Join Discord
Powered by
Anyone has an idea on how to make this?
# help
a
Anyone has an idea on how to make this?
e
I was building up SQL queries to retrieve a full object hierarchy from my DB and stumbled upon a pattern which may be useful here too. The idea is to use Postgres's native support for JSON objects / arrays to progressively build up your object hierarchy and return it all at once. The example I used is from the code generated by Zapatos, a TypeScript query builder: https://jawj.github.io/zapatos/#joins-as-nested-json (go down a bit and expand the generated SQL results)
For you, the goal would be to return an object with the following shape, right?
Copy code
json
{
  users: [ /* ... */ ],
  posts: [ /* ... */ ],
  other: [ /* ... */ ]
}
a
Yes. That is the exact shape of data I am looking for. Tables that has no relation, but I want to use to build the UI. I am taking a look at the link you've sent. Thank you.
e
I suggest setting up some test tables to play around with the proper query for you. The basic idea is: - Have a statement that gathers JSON objects: 
SELECT to_jsonb(users.*) as users_json FROM users WHERE ...
- Gather all the results into a JSON array: 
SELECT coalesce(jsonb_agg(users_json), '[]') as users_array
- Nest levels of JSON hierarchy using lateral joins: they basically allow you to write 
for
loops in SQL, by allowing a subquery to have access to variables in statements that surround it. Highly recommend reading up on them
I'll imagine a scenario, where you have a 
common_id
and you want to retrieve all posts and users that reference that ID + let's say you want to nest all the comments for each post (to show how nesting works)
Copy code
pgsql
create function get_users_and_posts(common_id uuid)
returns jsonb
language plpgsql
as $$
declare
    users jsonb;
    posts jsonb;
begin
    -- Gather all JSON objects from `users` into an array
    select coalesce(jsonb_agg(users.json), '[]')
    into users
    from (
        -- Get each row of users into a JSON object
        select to_jsonb(users.*) as json
        from public.users
        where users.common_id = common_id
    ) as users;
    -- Gather all JSON objects from `posts` into an array
    select coalesce(jsonb_agg(posts.json), '[]')
    into posts
    from (
        -- merge post data and the nested `comments` array into a single JSON object
        select to_jsonb(posts.*) || jsonb_build_object('comments', ljoin.comments) as json
        from public.posts
        -- gather comments
        left join lateral (
            select coalesce(jsonb_agg(comments.json), '[]') as comments
            from (
                select to_jsonb(comments.*) as json
                from public.comments
                -- the lateral join allows us to reference posts.id here!
                where post_id = posts.id
            ) as comments
        ) as ljoin on true -- we just want to iterate over comments
    ) as posts;
    return jsonb_build_object('users', users, 'posts', posts);
end;
$$
The code is somewhat heavy, but it's mostly the same thing going on
Still working on how to abstract this properly 🙂
PreviousNext
Powered by