package database import ( "github.com/go-pg/pg/v10" ) type BookLister interface { NewBookList(listID, title, username string, description []string) error AddBookToList(listID, bookID string) error DeleteBookFromList(listID, bookID string) error UpdateBookList(listID, title string, description []string) error GetBookList(listID string) (*BookList, error) GetListsByUser(username string) ([]BookList, error) GetListsByBook(bookID string) ([]BookList, error) } type BookList struct { ID int `pg:"type:serial"` ListID string `pg:"type:varchar(16)"` Title string Description []string `pg:"description,array"` UserID int `pg:"type:integer"` User *User `pg:"rel:has-one"` Books []Book `pg:"many2many:book_list_entries"` } type BookListEntry struct { ID int `pg:"type:serial"` BookListID int `pg:"type:integer"` BookList *BookList `pg:"rel:has-one"` BookID string `pg:"type:varchar(16)"` Book *Book `pg:"rel:has-one"` } func (db *pgDB) NewBookList(listID, title, username string, description []string) error { user, err := db.getUser(username) if err != nil { return err } _, err = db.sql.Model(&BookList{ ListID: listID, Title: title, Description: description, UserID: user.ID, }).Insert() return err } func (db *pgDB) AddBookToList(listID, bookID string) error { list, err := db.GetBookList(listID) if err != nil { return err } _, err = db.sql.Model(&BookListEntry{ BookListID: list.ID, BookID: bookID, }).Insert() return err } func (db *pgDB) DeleteBookFromList(listID, bookID string) error { list, err := db.GetBookList(listID) if err != nil { return err } _, err = db.sql.Model(&BookListEntry{}). Where("book_list_id = ? AND book_id = ?", list.ID, bookID). Delete() return err } func (db *pgDB) UpdateBookList(listID, title string, description []string) error { _, err := db.sql.Model(&BookList{}). Set("title = ?, description = ?", title, pg.Array(description)). Where("list_id = ?", listID). Update() return err } func (db *pgDB) GetBookList(listID string) (*BookList, error) { var bookList BookList err := db.sql.Model(&bookList). Relation("Books"). Relation("User"). Where("list_id = ?", listID). Select() return &bookList, err } func (db *pgDB) GetListsByUser(username string) ([]BookList, error) { var bookLists []BookList user, err := db.getUser(username) if err != nil { return bookLists, err } err = db.sql.Model(&bookLists). Relation("Books"). Relation("User"). Where("user_id = ?", user.ID). Select() return bookLists, err } func (db *pgDB) GetListsByBook(bookID string) ([]BookList, error) { var bookLists []BookList var bookListEntries []BookListEntry err := db.sql.Model(&bookListEntries). Relation("BookList"). Where("book_id = ?", bookID). Select() if err != nil || len(bookListEntries) == 0 { return bookLists, err } whereQuery := `list_id IN (` listIDs := make([]interface{}, len(bookListEntries)) for i, entry := range bookListEntries { whereQuery += "?" if i < len(bookListEntries)-1 { whereQuery += ", " } listIDs[i] = entry.BookList.ListID } whereQuery += ")" err = db.sql.Model(&bookLists). Relation("Books"). Relation("User"). Where(whereQuery, listIDs...). Select() return bookLists, err }