Counts that require joins/laterals

[rsparkyc]

Perhaps I'm missing tis is the documentation, but I can't seem to figure out how to get zapatos to do what I'd like here. Consider the following example, where Books have Chapters, and Chapters have Paragraphs. Let's say I have the following object hierarchy:

Book: id, name
Chapter: id, book_id
Paragraph: id, chapter_id

Let's say I wanted to run a query to return all books, and in another object (say count_metadata) have their number of chapters, and their number of paragraphs. I feel that I can perform the first 2 parts of this (books and their number of chapters) by doing the following:

Db.select('book', Db.all,
    {
        lateral: {
            count_metadata: Db.select('book', { id: Db.parent('id') },
                {
                    columns: [],
                    alias: 'sameBookQueriedForCounts',
                    lateral: {
                        chapter_count: Db.count('chapter', { book_id: Db.parent('id') })
                    }
                }
            )
        }
    }
);

This would return something like this:

{
  id: 1,
  name: "My Awesome Book",
  count_metadata: {
    chapter_count: 5
  }
}  

I could even get a list of paragraph counts for each chapter by doing this:

Db.select('book', Db.all,
    {
        lateral: {
            count_metadata: Db.select('book', { id: Db.parent('id') },
                {
                    columns: [],
                    alias: 'sameBookQueriedForCounts',
                    lateral: {
                        chapter_count: Db.count('chapter', { book_id: Db.parent('id') }),
                        paragraph_count: Db.select('chapter', { book_id: Db.parent('id') },
                            {
                                lateral: Db.count('paragraph', { chapter_id: Db.parent('id') })
                             }
                        )
                    }
                }
            )
        }
    }
);

Now I have something that resembles the following:

{
  id: 1,
  name: "My Awesome Book",
  count_metadata: {
    chapter_count: 5,
    paragraph_count: [3, 2, 7, 5, 1]
  }
}  

However, what I'm looking for is to get the total number of paragraphs per book, resulting to something like this:

{
  id: 1,
  name: "My Awesome Book",
  count_metadata: {
    chapter_count: 5,
    paragraph_count: 18
  }
}  

Is there a way to modify my count query for paragraphs to get the count of all the paragraphs for a book, or perhaps sum all the values it my paragraph counts? I couldn't figure out how to do that without adding a direct relationship from a paragraph to a book. I also thought perhaps I could do some passthrough laterals in my count query, but counts don't support laterals within their options. Am I missing something?

[jawj]

OK, so you can use passthrough laterals to simplify the list-of-paragraph-counts result:

const chcounts = await db.select('books', db.all, {
  columns: ['title'],
  lateral: {
    chapterParaCounts: db.select('chapters', { bookId: db.parent('id') }, {
      lateral: db.count('paragraphs', { chapterId: db.parent('id') })
    })
  }
}).run(pool);

[
  { "title": "Love in the Time of Cholera", "chapterParaCounts": [ 1 ] },
  { "title": "Pride and Prejudice", "chapterParaCounts": [ 2, 3 ] }
]

For your sum-of-paragraph counts example I think we currently have to resort to some manual SQL:

const paracounts = await db.select('books', db.all, {
  columns: ['title'],
  lateral: {
    paraCount: db.sql<s.chapters.SQL>`
      SELECT sum("my_join"."result") AS "result" FROM "chapters"
      LEFT JOIN LATERAL (${db.count('paragraphs', { chapterId: db.sql`${db.self} = ${"chapters"}.${"id"}` })}) 
      AS "my_join" ON TRUE
      WHERE ${{ bookId: db.parent('id') }}`
  }
}).run(pool);

[
  { "title": "Love in the Time of Cholera", "paraCount": 1 },
  { "title": "Pride and Prejudice", "paraCount": 5 },
]

But it feels like we could probably make the shortcuts more flexible, to support this kind of thing. I'll ponder that ...

[rsparkyc]

cool, I think making the shortcuts more flexible would be awesome. I love the shortcuts so much I didn't want to resort to writing sql in the code, and instead was thinking I could make a view that links the paragraphs to the books, and then query against that view when needing those counts.