I am currently working on a library website and we...
# sqlj
joshcowan25
04/18/2022, 5:00 AMI 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)?
joshcowan25
04/18/2022, 5:02 AMThanks
joshcowan25
04/18/2022, 5:06 AMJust 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 AMYes I can help.
tourdownunder
04/18/2022, 5:39 AMsupabase uses postgrest under the hood so it will always convert the data into json regardless.
tourdownunder
04/18/2022, 5:43 AMIf 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
`tourdownunder
04/18/2022, 5:55 AMTo 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 AMThanks! What is the difference between CREATE VIEW and CREATE OR REPLACE?
t
tourdownunder
04/19/2022, 11:51 AMOr 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.