Part 133: Enhancing Comment Display with User Names in a Relational Database

[App] Authentication User Database

[App] Authentication User Database

When building a web application, displaying user comments is a common feature that enhances user engagement. However, as we implement authentication and database relationships, we may encounter challenges, such as displaying associated user names alongside their comments. In this post, we’ll explore how to tackle these issues by leveraging relational database features and optimizing our queries.

The Problem: Missing User Names

After updating our database schema to store userId in the Comment model, we noticed that user names were missing from our CommentList. This was because our query fetched only the Comment fields, omitting the associated User fields, including the name.

Understanding the Cause

1. Database Relationships: We established a relationship between Comments and Users by storing userId in the Comment model. However, by default, our database queries didn’t fetch related User data.

2. Prisma Behavior: Prisma, our ORM, doesn’t automatically include related tables in queries. Thus, while our Comment objects had userId, they lacked the user name data.

Solution: Include User Data in Queries

To solve this, we need to modify our database query to include the User relation with each Comment.

Step 1: Modify the Query

Using Prisma's include option, we can specify that we want to load the User relation for each Comment.

// File path: lib/comments.js

export async function getComments(slug) {
  return await db.comment.findMany({
    where: { slug },
    orderBy: { postedAt: 'desc' },
    include: {
      user: true,
    },
  });
}

Step 2: Update the User Interface

After updating our query, each Comment object now contains a nested user object with the user's name. We need to adjust our CommentList component to display comment.user.name instead of comment.user.

// File path: components/CommentList.jsx

export default async function CommentList({ slug }) {
  const comments = await getComments(slug);
  console.log('[CommentList]', comments);
  if (comments.length === 0) {
    return <p className="italic mt-3">No comments yet.</p>
  }
  return (
    <ul className="border mt-3 rounded">
      {comments.map((comment) => (
        <li key={comment.id}
          className="border-b px-3 py-2 last:border-none odd:bg-orange-100">
          <div className="flex gap-3 pb-1 text-slate-500">
            <UserCircleIcon className="h-6 w-6" />
            {comment.user.name}
          </div>
          <p className="italic">
            {comment.message}
          </p>
        </li>
      ))}
    </ul>
  );
}

Step 3: Optimize the Query

While our solution works, it’s not optimal. Fetching all user columns is unnecessary when we only need the name. We can optimize this by using the select option to specify which columns to retrieve.

// File path: lib/comments.js

export async function getComments(slug) {
  return await db.comment.findMany({
    where: { slug },
    orderBy: { postedAt: 'desc' },
    include: {
      user: {
        select: { name: true },
      },
    },
  });
}

Understanding Prisma's Include and Select

• Include: Specifies which relations to load, resulting in a join operation in SQL terms.

• Select: Specifies which columns to retrieve from the table, allowing for precise data fetching.

By using these features, we efficiently retrieve only the necessary data, reducing overhead and improving performance.

Testing the Implementation

After implementing these changes, it’s vital to test the functionality:

1. Sign In: Log in as a user and ensure the comments display the correct user names.

2. Sign Out: Verify that the comment form is not visible to unauthenticated users.

3. Multiple Users: Test with different users, ensuring each comment correctly attributes the author.

Conclusion

By leveraging database relationships and optimizing queries, we've resolved the issue of missing user names in our comment list. These enhancements not only improve user experience but also demonstrate the power of relational databases in web applications. As you build and refine your application, consider how these principles can be applied to other features for more efficient and robust solutions.