DT
Drizzle Team•7mo ago
march

How to accomplish the following JSON format on retrievals

How can I format my query so that drizzle returns a property like so: 
Book {
    bookId: 1,
    title: 'Book title',
    author: 'Book author',
    synopsis: 'Book synopsis',
    datePublished: '2024-01-06'
    genres: [
        "g1",
        "g2"
    ],
    rating: 10.0,
    coverImage: 'some/image/path.png'
}
Book {
    bookId: 1,
    title: 'Book title',
    author: 'Book author',
    synopsis: 'Book synopsis',
    datePublished: '2024-01-06'
    genres: [
        "g1",
        "g2"
    ],
    rating: 10.0,
    coverImage: 'some/image/path.png'
}
6 Replies
march
marchOP•7mo ago
My postgres db looks like this: 
CREATE TABLE books (
    book_id         SERIAL PRIMARY KEY,
    title             VARCHAR(100) UNIQUE NOT NULL,
    author             VARCHAR(50) NOT NULL,
    synopsis         TEXT NOT NULL,
    date_published     DATE DEFAULT CURRENT_DATE NOT NULL,
    rating             DECIMAL(3, 1) CHECK(rating >= 0.0 AND rating <= 10.0) DEFAULT 0.0 NOT NULL,
    cover_image     VARCHAR(255) -- will need a default book cover
);
        
CREATE TABLE book_stories (
    book_id     INT     REFERENCES books(book_id) ON DELETE CASCADE,
    story_id     SERIAL     PRIMARY KEY,
    story         TEXT     NOT NULL
);

CREATE TABLE genres (
    genre_id     SERIAL PRIMARY KEY,
    genre         VARCHAR(40) UNIQUE NOT NULL
);


CREATE TABLE book_genres (
    book_id     INT REFERENCES books(book_id) ON DELETE CASCADE,
    genre_id     INT REFERENCES genres(genre_id) ON DELETE CASCADE,
    PRIMARY KEY (genre_id, book_id)
);
CREATE TABLE books (
    book_id         SERIAL PRIMARY KEY,
    title             VARCHAR(100) UNIQUE NOT NULL,
    author             VARCHAR(50) NOT NULL,
    synopsis         TEXT NOT NULL,
    date_published     DATE DEFAULT CURRENT_DATE NOT NULL,
    rating             DECIMAL(3, 1) CHECK(rating >= 0.0 AND rating <= 10.0) DEFAULT 0.0 NOT NULL,
    cover_image     VARCHAR(255) -- will need a default book cover
);
        
CREATE TABLE book_stories (
    book_id     INT     REFERENCES books(book_id) ON DELETE CASCADE,
    story_id     SERIAL     PRIMARY KEY,
    story         TEXT     NOT NULL
);

CREATE TABLE genres (
    genre_id     SERIAL PRIMARY KEY,
    genre         VARCHAR(40) UNIQUE NOT NULL
);


CREATE TABLE book_genres (
    book_id     INT REFERENCES books(book_id) ON DELETE CASCADE,
    genre_id     INT REFERENCES genres(genre_id) ON DELETE CASCADE,
    PRIMARY KEY (genre_id, book_id)
);
currently my tester function looks like this: 
const getAllBooks = async () => {
    const result = await db.select({
        bookId: Books.bookId,
        title: Books.title,
        author: Books.author,
        synopsis: Books.synopsis,
        datePublished: Books.datePublished,
        rating: Books.rating,
        coverImage: Books.coverImage,
        genres: Genres.genre,
      })
      .from(Books)
      .innerJoin(BookGenres, eq(Books.bookId, BookGenres.bookId))
      .innerJoin(Genres, eq(BookGenres.genreId, Genres.genreId));

    console.log(result);

    pool.end();
}
const getAllBooks = async () => {
    const result = await db.select({
        bookId: Books.bookId,
        title: Books.title,
        author: Books.author,
        synopsis: Books.synopsis,
        datePublished: Books.datePublished,
        rating: Books.rating,
        coverImage: Books.coverImage,
        genres: Genres.genre,
      })
      .from(Books)
      .innerJoin(BookGenres, eq(Books.bookId, BookGenres.bookId))
      .innerJoin(Genres, eq(BookGenres.genreId, Genres.genreId));

    console.log(result);

    pool.end();
}
Because a book can have multiple genres, I wanted them to be shown as an array like in the JSON format above, atm drizzle obtains the genres as separate entries For example: 
{
    bookId: 2,
    title: 'To Kill a Mockingbird',
    author: 'Harper Lee',
    synopsis: 'Set in small-town Alabama, the novel is a bildungsroman, or coming-of-age story, and chronicles the childhood of Scout and Jem Finch as their father Atticus defends a Black man falsely accused of rape.',
    datePublished: '1960-07-11',
    rating: '9.4',
    coverImage: null,
    genres: 'Southern Gothic'
  },
  {
    bookId: 2,
    title: 'To Kill a Mockingbird',
    author: 'Harper Lee',
    synopsis: 'Set in small-town Alabama, the novel is a bildungsroman, or coming-of-age story, and chronicles the childhood of Scout and Jem Finch as their father Atticus defends a Black man falsely accused of rape.',
    datePublished: '1960-07-11',
    rating: '9.4',
    coverImage: null,
    genres: 'Bildungsroman'
  }
{
    bookId: 2,
    title: 'To Kill a Mockingbird',
    author: 'Harper Lee',
    synopsis: 'Set in small-town Alabama, the novel is a bildungsroman, or coming-of-age story, and chronicles the childhood of Scout and Jem Finch as their father Atticus defends a Black man falsely accused of rape.',
    datePublished: '1960-07-11',
    rating: '9.4',
    coverImage: null,
    genres: 'Southern Gothic'
  },
  {
    bookId: 2,
    title: 'To Kill a Mockingbird',
    author: 'Harper Lee',
    synopsis: 'Set in small-town Alabama, the novel is a bildungsroman, or coming-of-age story, and chronicles the childhood of Scout and Jem Finch as their father Atticus defends a Black man falsely accused of rape.',
    datePublished: '1960-07-11',
    rating: '9.4',
    coverImage: null,
    genres: 'Bildungsroman'
  }
rather than what I want which looks like this: 
{
    bookId: 2,
    title: 'To Kill a Mockingbird',
    author: 'Harper Lee',
    synopsis: 'Set in small-town Alabama, the novel is a bildungsroman, or coming-of-age story, and chronicles the childhood of Scout and Jem Finch as their father Atticus defends a Black man falsely accused of rape.',
    datePublished: '1960-07-11',
    rating: '9.4',
    coverImage: null,
    genres: ['Southern Gothic', 'Bildungsroman']
  }
{
    bookId: 2,
    title: 'To Kill a Mockingbird',
    author: 'Harper Lee',
    synopsis: 'Set in small-town Alabama, the novel is a bildungsroman, or coming-of-age story, and chronicles the childhood of Scout and Jem Finch as their father Atticus defends a Black man falsely accused of rape.',
    datePublished: '1960-07-11',
    rating: '9.4',
    coverImage: null,
    genres: ['Southern Gothic', 'Bildungsroman']
  }
Not sure if its possible to back populate the books schema in this case? Using book_genres as a pivot to get the ids and use genres to get the genre names?
aaliyah ^-^
aaliyah ^-^•7mo ago
dude are you stupid
march
marchOP•7mo ago
lol? please enlighten me @helencik ?
aaliyah ^-^
aaliyah ^-^•7mo ago
idk figure out you're pretty stupid though this is not a drizzle thging
march
marchOP•7mo ago
HAHAHAHAHA nah but you're not real thank you for the laugh though
aaliyah ^-^
aaliyah ^-^•7mo ago
Want results from more Discord servers?
Add your server