const usersWithPosts = await prisma.user.findMany({
where: { isActive: true },
include: {
posts: {
where: { published: true },
orderBy: { createdAt: 'desc' },
take: 5,
select: {
id: true,
title: true,
slug: true,
createdAt: true,
},
},
_count: {
select: { posts: { where: { published: true } } },
},
},
orderBy: { createdAt: 'desc' },
take: 20,
});
async function paginatePosts(cursor?: string, take: number = 20) {
const posts = await prisma.post.findMany({
where: { published: true },
take: take + 1,
...(cursor && {
cursor: { id: cursor },
skip: 1,
}),
orderBy: { createdAt: 'desc' },
select: {
id: true,
title: true,
slug: true,
author: { select: { name: true } },
createdAt: true,
},
});
const hasMore = posts.length > take;
const items = hasMore ? posts.slice(0, take) : posts;
return {
items,
nextCursor: hasMore ? items[items.length - 1].id : null,
hasMore,
};
}
function buildPostFilter(params: {
search?: string;
categoryId?: string;
authorId?: string;
publishedAfter?: Date;
}): Prisma.PostWhereInput {
const where: Prisma.PostWhereInput = { published: true };
if (params.search) {
where.OR = [
{ title: { contains: params.search, mode: 'insensitive' } },
{ content: { contains: params.search, mode: 'insensitive' } },
];
}
if (params.categoryId) {
where.categories = {
some: { categoryId: params.categoryId },
};
}
if (params.authorId) {
where.authorId = params.authorId;
}
if (params.publishedAfter) {
where.publishedAt = { gte: params.publishedAfter };
}
return where;
}
const user = await prisma.user.upsert({
where: { email: 'jane@example.com' },
update: { name: 'Jane Updated' },
create: { email: 'jane@example.com', name: 'Jane Doe' },
});
const stats = await prisma.post.aggregate({
where: { published: true },
_count: true,
_avg: { viewCount: true },
_max: { viewCount: true },
});
const postsByMonth = await prisma.post.groupBy({
by: ['authorId'],
where: { published: true },
_count: { id: true },
_sum: { viewCount: true },
orderBy: { _count: { id: 'desc' } },
take: 10,
});
async function transferCredits(fromId: string, toId: string, amount: number) {
return prisma.$transaction(async (tx) => {
const sender = await tx.user.findUnique({
where: { id: fromId },
select: { credits: true },
});
if (!sender || sender.credits < amount) {
throw new Error('Insufficient credits');
}
await tx.user.update({
where: { id: fromId },
data: { credits: { decrement: amount } },
});
await tx.user.update({
where: { id: toId },
data: { credits: { increment: amount } },
});
return tx.transaction.create({
data: {
fromUserId: fromId,
toUserId: toId,
amount,
type: 'TRANSFER',
},
});
}, {
maxWait: 5000,
timeout: 10000,
isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
});
}
const [updatedUser, newPost] = await prisma.$transaction([
prisma.user.update({ where: { id: userId }, data: { postCount: { increment: 1 } } }),
prisma.post.create({ data: { title: 'New Post', authorId: userId } }),
]);
const result = await prisma.$queryRaw<Array<{ id: string; rank: number }>>`
SELECT id, RANK() OVER (ORDER BY view_count DESC) as rank
FROM posts
WHERE published = true
AND created_at > ${thirtyDaysAgo}
LIMIT ${limit}
`;
const searchResults = await prisma.$queryRaw`
SELECT p.id, p.title, p.slug,
ts_rank(to_tsvector('english', p.title || ' ' || p.content), query) AS rank
FROM posts p, to_tsquery('english', ${searchQuery}) query
WHERE p.published = true
AND to_tsvector('english', p.title || ' ' || p.content) @@ query
ORDER BY rank DESC
LIMIT 20
`;
