Drizzle Team•7d ago

Get all authors with latest book

With the classic library/author/book example, how can i query all authors, each with their latest book? (Assuming some sort of book.releaseDate column) The goal is to not load all books from the DB and perform the "latest" filter in the Node.js backend code. With regular SQL, one possible solution is a subquery. I've searched for existing solutions that use Drizzle but couldn't find any, so i asked ChatGPT and it went straight to "you gotta use raw SQL for the subquery" 💀 I hope this is one of the many cases of SmartAutocompleteGPT being wrong.

Solution:

Assuming at least one to many authors -> books ```ts const authorsWithLatestBook = db.query.authors.findMany({...

Jump to solution

7 Replies

Chev•5d ago

share what you have so far

functionOP•5d ago

SELECT a.author_id, a.author_name, b.book_id, b.book_title, b.release_date
FROM authors a
JOIN books b ON a.author_id = b.author_id
WHERE b.release_date = (
    SELECT MAX(b1.release_date)
    FROM books b1
    WHERE b1.author_id = b.author_id
);

SELECT a.author_id, a.author_name, b.book_id, b.book_title, b.release_date
FROM authors a
JOIN books b ON a.author_id = b.author_id
WHERE b.release_date = (
    SELECT MAX(b1.release_date)
    FROM books b1
    WHERE b1.author_id = b.author_id
);

Solution

TOSL•5d ago

Assuming at least one to many authors -> books

const authorsWithLatestBook = db.query.authors.findMany({
  with: {
    books: {
      orderBy: (books, {desc}) => [desc(books.releaseDate),
      limit: 1
    }
  }
})

const authorsWithLatestBook = db.query.authors.findMany({
  with: {
    books: {
      orderBy: (books, {desc}) => [desc(books.releaseDate),
      limit: 1
    }
  }
})

I recommend you take the time to read the docs fully. They are good and you would definitely have come to same answer as this is a pretty simple query. orderby desc gives latest book first limit 1 gives only first book

functionOP•5d ago

can't test right now but that looks like exactly what i'm looking for, thank you. your recommendation to read the docs hurts a bit because i spent a lot of time reading the docs, specifically looking for solutions to this greatest-n-per-group problem, and couldn't come up with something. i was unaware you could use "limit" inside "with".

TOSL•5d ago

This isn't an attack. Please don't take offense. The docs aren't perfect but they are setup to answer most questions. I only say this because it took so many hours for you to get an answer here. https://orm.drizzle.team/docs/rqb#limit--offset

Drizzle ORM - Query

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

functionOP•5d ago

i didn't take it as an attack, i just felt a bit like i just didn't have the skills to find or come up with this, since the subquery example doesn't use limit but an aggregate function. i just wanted to express that i think me not finding a solution wasn't for lack of trying. anyway, thanks a lot for the solution and for the docs link.

TOSL•5d ago

I wouldn't worry too much about it. There's always a learning curve for anything. I've been using Drizzle for quite a while now. Just browse the docs and play around on https://www.drizzle.run you're welcome

Gaming

Programming

Get all authors with latest book

Did you find this page helpful?