1.Retrieve users along with their posts that have a specific tag, such as “tech”.
const usersWithTechPosts = await userRepository
.createQueryBuilder('user')
.leftJoinAndSelect('user.posts', 'post')
.leftJoinAndSelect('post.tags', 'tag')
.where('tag.name = :tagName', { tagName: 'tech' })
.getMany();
2.Update titles of posts belonging to a specific user.
await postRepository
.createQueryBuilder()
.update(Post)
.set({ title: 'New Title' })
.where('user.id = :userId', { userId: 1 })
.execute();
3.Delete posts that have no associated comments.
await postRepository
.createQueryBuilder()
.delete()
.from(Post)
.where('comments IS EMPTY')
.execute();
4.Retrieve users who have the highest number of posts.
const mostActiveUsers = await userRepository
.createQueryBuilder('user')
.leftJoinAndSelect('user.posts', 'post')
.addOrderBy('COUNT(post.id)', 'DESC')
.groupBy('user.id')
.getMany();
5.Find users who have posts tagged with both “tech” and “programming”.
const usersWithTags = await userRepository
.createQueryBuilder('user')
.leftJoinAndSelect('user.posts', 'post')
.leftJoinAndSelect('post.tags', 'tag')
.where('tag.name IN (:...tagNames)', { tagNames: ['tech', 'programming'] })
.groupBy('user.id')
.having('COUNT(DISTINCT tag.id) = :tagCount', { tagCount: 2 })
.getMany();
6.Find posts with the highest count of associated comments.
const mostCommentedPosts = await postRepository
.createQueryBuilder('post')
.leftJoinAndSelect('post.comments', 'comment')
.addOrderBy('COUNT(comment.id)', 'DESC')
.groupBy('post.id')
.getMany();
7.Retrieve posts by a user that were created between specific dates.
const userPostsByDate = await postRepository
.createQueryBuilder('post')
.where('post.user = :userId', { userId: 1 })
.andWhere('post.createdAt BETWEEN :startDate AND :endDate', {
startDate: '2023-01-01',
endDate: '2023-08-01',
})
.getMany();
8.Retrieve users and their posts, ordered by the creation date of posts.
const usersWithPostsOrdered = await userRepository
.createQueryBuilder('user')
.leftJoinAndSelect('user.posts', 'post')
.addOrderBy('post.createdAt', 'ASC')
.getMany();
9.Find users who have posted more than 3 times in the past month.
const activeUsers = await userRepository
.createQueryBuilder('user')
.leftJoinAndSelect('user.posts', 'post')
.where('post.createdAt >= :lastMonth', {
lastMonth: new Date(new Date().setMonth(new Date().getMonth() - 1)),
})
.groupBy('user.id')
.having('COUNT(post.id) > :postCount', { postCount: 3 })
.getMany();
10.Retrieve users along with their latest post and the count of comments on that post.
const usersWithLatestPostAndCommentCount = await userRepository
.createQueryBuilder('user')
.leftJoinAndSelect(
subQuery => subQuery
.from(Post, 'post')
.where('post.user = user.id')
.orderBy('post.createdAt', 'DESC')
.limit(1),
'latestPost',
'latestPost.user = user.id'
)
.leftJoinAndSelect(
subQuery => subQuery
.from(Comment, 'comment')
.where('comment.postId = latestPost.id'),
'latestPostComments'
)
.addSelect('COUNT(DISTINCT latestPostComments.id)', 'commentCount')
.groupBy('user.id')
.getRawMany();
11.Find users who have posted the most recent post.
const latestPostUsers = await userRepository
.createQueryBuilder('user')
.leftJoinAndSelect('user.posts', 'post')
.addSelect('MAX(post.createdAt)', 'latestPostCreatedAt')
.groupBy('user.id')
.having('MAX(post.createdAt) = latestPostCreatedAt')
.getRawMany();
12.Retrieve posts with the highest average rating based on ratings given.
const highestRatedPosts = await postRepository
.createQueryBuilder('post')
.leftJoinAndSelect('post.ratings', 'rating')
.addSelect('AVG(rating.value)', 'averageRating')
.groupBy('post.id')
.orderBy('averageRating', 'DESC')
.getRawMany();
13.Retrieve users who have both commented on posts and provided ratings.
const usersWithCommentsAndRatings = await userRepository
.createQueryBuilder('user')
.innerJoinAndSelect('user.comments', 'comment')
.innerJoin('user.ratings', 'rating')
.where('comment.id IS NOT NULL')
.getMany();
14.Retrieve posts that have both comments and ratings.
const postsWithCommentsAndRatings = await postRepository
.createQueryBuilder('post')
.leftJoinAndSelect('post.comments', 'comment')
.leftJoinAndSelect('post.ratings', 'rating')
.where('comment.id IS NOT NULL')
.andWhere('rating.id IS NOT NULL')
.getMany();
15.Find users who have posted at least once in each month of the current year.
const usersWithMonthlyPosts = await userRepository
.createQueryBuilder('user')
.innerJoinAndSelect('user.posts', 'post')
.where(`EXTRACT(YEAR FROM post.createdAt) = :year`, { year: new Date().getFullYear() })
.groupBy('user.id')
.having('COUNT(DISTINCT EXTRACT(MONTH FROM post.createdAt)) = 12')
.getMany();
16.Retrieve users who have posts with common tags.
const usersWithCommonTags = await userRepository
.createQueryBuilder('user')
.leftJoinAndSelect('user.posts', 'post')
.leftJoinAndSelect('post.tags', 'tag')
.groupBy('user.id')
.having('COUNT(DISTINCT tag.id) > 1')
.getMany();
17.Retrieve users who have commented on each of their own posts.
const usersWithSelfComments = await userRepository
.createQueryBuilder('user')
.innerJoinAndSelect('user.posts', 'post')
.innerJoinAndSelect('user.comments', 'comment', 'comment.postId = post.id')
.groupBy('user.id')
.having('COUNT(DISTINCT post.id) = COUNT(DISTINCT comment.postId)')
.getMany();
18.Retrieve the latest posts from users who have posted more than 10 posts.
const latestPostsFromActiveUsers = await postRepository
.createQueryBuilder('post')
.leftJoinAndSelect('post.user', 'user')
.where('user.id IN ' +
postRepository.createQueryBuilder('sub')
.select('subUser.id')
.from(User, 'subUser')
.leftJoin('subUser.posts', 'subPost')
.groupBy('subUser.id')
.having('COUNT(subPost.id) > 10')
.getQuery()
)
.orderBy('post.createdAt', 'DESC')
.getMany();
19.Find users who have commented on their own posts and also on posts of users they follow.
const usersWithFollowedUserComments = await userRepository
.createQueryBuilder('user')
.leftJoinAndSelect('user.comments', 'comment')
.leftJoin('user.following', 'followedUser')
.leftJoin('followedUser.posts', 'followedUserPost')
.leftJoin('followedUserPost.comments', 'followedUserComment', 'followedUserComment.userId = user.id')
.where('comment.userId = user.id OR followedUserComment.userId = user.id')
.getMany();
20.Retrieve posts from users who have both rated and commented on their own posts.
const postsWithUserEngagement = await postRepository
.createQueryBuilder('post')
.leftJoinAndSelect('post.user', 'user')
.leftJoinAndSelect('post.ratings', 'rating', 'rating.userId = user.id')
.leftJoinAndSelect('post.comments', 'comment', 'comment.userId = user.id')
.where('rating.id IS NOT NULL AND comment.id IS NOT NULL')
.getMany();
21.Find users who have posted and commented the most in a specific month.
const usersWithMostActivityInMonth = await userRepository
.createQueryBuilder('user')
.leftJoinAndSelect('user.posts', 'post')
.leftJoinAndSelect('user.comments', 'comment')
.where(`EXTRACT(YEAR FROM post.createdAt) = :year`, { year: 2023 })
.andWhere(`EXTRACT(MONTH FROM post.createdAt) = :month`, { month: 8 })
.groupBy('user.id')
.orderBy('COUNT(DISTINCT post.id) + COUNT(DISTINCT comment.id)', 'DESC')
.getMany();