Graphql query against two tables

Get Help and Help Others
1

Here’s an example setup I have:

I have Blog posts that are shown on a landing page. I have a PostsCell that retrieves the blog posts. A post includes id, title, body, date.

If you click into a blog post, I have a comment section. I load those comments using a CommentsCell and pass in the blog post ID to select the correct comments. A comment includes an id, author, text, date.

All that works great and was super simple to set up. Now I want to order the blog posts shown on the landing page by the number of comments. Can I query my Posts table and my Comments table at the same time in my PostsCell? Or have I set up my DB schema incorrectly?

2

Okay, I figured out that I can do the following query and then access both posts and commentCount in my Success component but I still don’t know how to get they commentCount for a specific post

export const QUERY = gql`
query PostsQuery {
posts {
id
title
body
}

commentsCount: commentsCount(postId: 1)

}
`

3

I can do this in SQL but I’m not sure of the equivalent Prisma syntax and I’d probably spend an hour trying to experiment and figure it out. But, you could also use db.$queryRaw() and just use this SQL directly:

SELECT   Post.*, COUNT(Comment.id) AS commentsCount
FROM     Post INNER JOIN Comment on Post.id = Comment.postId
GROUP BY Comment.postId

Then you just need to add commentCount as a field on Post in the SDL. Then you can query:

export const QUERY = gql`
  query PostsQuery {
    posts {
      id
      title
      body
      commentsCount
    }
  }
}

BONUS: If you want the records pre-sorted back from the API (let the database so that work for you) you can order the results by the number of comments:

SELECT   Post.*, COUNT(Comment.id) AS commentsCount
FROM     Post INNER JOIN Comment on Post.id = Comment.postId
GROUP BY Comment.id
ORDER BY commentsCount DESC
2 Likes
4

If you like your first version you could just update the commentsCount service to include the postId along with the count of comments (and add postId to the SDL so it can be returned from the query):

export const commentsCount = async ({ postId }) => {
  const count = await db.comment.aggregate({ 
    where: { postId }
  }, 
  { 
    _count: { id: true }
  })

  return { postId, commentsCount: count._count.id }
})

(I’ve never actually done a count with Prisma so that might not be correct, but should be close! Why _count and not just count?)

5

Thanks - this worked. I had to change the SQL group by slightly but this is returning the correct count.

SELECT   Post.*, COUNT(Comment.id) AS commentsCount
  FROM     Post INNER JOIN Comment on Post.id = Comment.postId
  GROUP BY **Post.id**

I will take a look at the Prisma suggestion tomorrow morning. Thanks again.

1 Like
6

I had to change the SQL group by slightly

Whoops! I just updated my post, should have been GROUP BY Comment.postId but GROUP BY Post.id works as well!

7

I have this working now. Here’s what I did:

Step 1: Set up a Prisma relation on my Post and Comment tables. schema.prisma file changes:

Post table additions:
comments Comment[]

Comment table additions:

postId    Int
post      Post      @relation(fields: [postId], references: [id])

I then ran yarn rw prisma migrate dev

Next, I changed my posts service (posts.ts) to

export const posts = () => {
  const posts = db.post.findMany({
    include: {
      _count: {
        select: { comments: true },
      },
    },
  })

  return posts
}

The Prisma docs for counting relations are actually pretty good

Next, I updated my graphql posts.sdl.ts by adding in a new type

 type Counter {
    comments: Int
  }

and updating my posts type

 type Post {
    id: Int!
    title: String!
    body: String!
    createdAt: DateTime!
    _count: Counter
  }

And finally I updated my PostsCell query

export const QUERY = gql`
  query PostsQuery {
    posts {
      id
      title
      body
      _count {
        comments
      }
    }
  }
`

Example output:

{
        "id": 2,
        "title": "qwerty",
        "body": "qwerty",
        "_count": {
          "comments": 12
        }
 },

Thanks for putting me in the correct direction Rob!

2 Likes
8

Ahh interesting, I didn’t know you could { include: { _count } } like that! Have you tried turning on query logging to see what the resulting SQL query looks like? I wonder if it’s nice and efficient like the INNER JOIN one we wrote manually, or if it’s doing an N+1 query, which could be bad (depending on how many posts you have).

If you want to try, go to api/src/lib/db.js and add query to these arrays:

export const db = new PrismaClient({
  log: emitLogLevels(['query', 'info', 'warn', 'error']),
})

handlePrismaLogging({
  db,
  logger,
  logLevels: ['query', 'info', 'warn', 'error'],
})
9

Here’s what got logged out from the query level log:

query: “SELECT main.Post.id, main.Post.title, main.Post.body, main.Post.createdAt, aggr_selection_0_Comment._aggr_count_comments FROM main.Post LEFT JOIN (SELECT main.Comment.postId, COUNT(*) AS _aggr_count_comments FROM main.Comment GROUP BY main.Comment.postId) AS aggr_selection_0_Comment ON (main.Post.id = aggr_selection_0_Comment.postId) ON (main.Post.id = aggr_selection_0_Comment.postId) WHERE 1=1 LIMIT ? OFFSET ?”
api | params: “[Redacted]”
api | duration: “1”
api | target: “quaint::connector::metrics”
api | prisma: {
api | “clientVersion”: “3.5.0”
api | }

1 Like
10

That might actually be pretty efficient! Nice work Prisma!