https://supabase.com/ logo
#sql
Title
# sql
j

joshcowan25

04/18/2022, 5:00 AM
I am currently working on a library website and we need to use DublinCore for the system. The database was design to be the most atomic possible. So we have a table books that contains main info about the books, but we also have a BY table that contains ID to books, authors, and then the Authors table it self. We have those Linking table for many relationship. Same thing goes for subjects, where we have a isAbout table and a Subjects table. When we access to a book page, we need info about the book, the authors, the subjects... So I tought about it and I think that It would be better to create a postgres function returning everything about a book then making 5 normal supabase requests. I watched some videos about functions, but it ain't more clear for me... What would I return, a JSONB object? How do I return it? ... Can anyone help we write this function or just give me tips on how to do it (or examples)?
Thanks
Just to be sure I'm clear, BY is a junction table between books and authors; it contains bookID and authorID Same goes for isAbout, it's a junction table between books and subjects containing bookID and subjectID
t

tourdownunder

04/18/2022, 5:38 AM
Yes I can help.
supabase uses postgrest under the hood so it will always convert the data into json regardless.
If you want to show all authors and per author have an array of books per row of author you will be able to use a postres view for that.
Copy code
sql
CREATE VIEW author_with_books AS
select a.*, array_agg(b.name) from author a left outer join books b ON b.author = a.id 
group by a.*; -- not sure this is allowed

`
This will be a bit more complicated with your many to many table though the principle is the same.
To remove the group by you can you use a subquery
Copy code
sql
CREATE VIEW author_with_books AS
select 
a.*, 
(SELECT array_agg(row_to_json(b) from books b inner join bv ON b.id = bv.bookid where bv.authorid = a.id) 
from author a;
j

joshcowan25

04/19/2022, 4:55 AM
Thanks! What is the difference between CREATE VIEW and CREATE OR REPLACE?
t

tourdownunder

04/19/2022, 11:51 AM
Or replace is handy if you edit the view. Add a join / col etc and what to quickly replace the existing view with what is updated without dropping first.