Count or Include filtered relations prisma

Question

I am currently stuck on a problem with my prisma queries.

I have an asset which has a 1 to Many relationship to views. I am trying to perform a findMany() on assets which returns either;

1. The asset with a list of views created within the last day
2. Or the asset with a count of views created in the last day

Finally I need to be able to orderBy this count or the count of views in my include statement. (this is what I am stuck on)

return await prisma.asset.findMany({
  take: parseInt(pageSize),
  skip: (pageSize * pageNumber),
  include: {
    _count: {
      select: {
        views: true
      },
    },
    views: {
      where: {
        createdAt: dateFilter
      },
    },
    likes: {
      where: {
        createdAt: dateFilter
      }
     },
    transactions: true,
  },
  orderBy: { views: { _count: 'desc' } }

My queries does correctly return only views in my date range but how do I go about ordering the assets based on the count of these views. I have been stuck for quite some time on this. My raw SQL is not strong enough to write it from scratch at the moment.

If anyone has any ideas, thanks.

Answer 1

Will something like this work?

// First we group the views, with pagination
const groupedViews = await prisma.view.groupBy({
    take: 10,
    skip: 0,
    by: ['postId'],
    where: { createdAt: dateFilter },
    _count: { postId: true },
    orderBy: { _count: { postId: 'desc' } },
});

// Fetch the posts from the grouped views
const _posts = await prisma.post.findMany({
    where: {
        id: { in: groupedViews.map(({ postId }) => postId) },
    },
    include: {
        _count: { select: { views: true } },
        views: { where: { createdAt: dateFilter } },
    },
});

// Map the fetched posts back for correct ordering
const posts = groupedViews.map(({ postId }) =>
    _posts.find(({ id }) => id === postId)
);

Model:

model Post {
  id    String @id @default(cuid())
  views View[]
}

model View {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  postId    String
  post      Post     @relation(fields: [postId], references: [id])
}

This uses 2 separate queries, but does not require raw sql