Understanding Friends Logic with MySQL

As a developer, managing relationships between users can be complex. In this article, we’ll explore how to get all users that the logged in user is not friends with using MySQL.

Background and Context

The problem presented involves two tables: users and friends. The users table contains information about each user, while the friends table represents a many-to-many relationship between users. In this relationship, one user can be friends with multiple other users, and those relationships are stored in the friends table.

For example, let’s say we have two users: billy32 (with ID 3) and johnny28 (with ID 6). They can be friends with each other, as well as with others. The friendship between billy32 and johnny28 is represented by the following rows in the friends table:

To get all users that are friends with the currently logged in user, we can use a LEFT JOIN and filter on the user_id column. However, to find users who are not friends with the logged in user, we need to approach this problem differently.

Understanding the Problem

The key insight here is that we’re looking for an intersection of sets. Instead of finding all users whose IDs exist in the absence of a friend ID, we want to find all users whose IDs do not appear as a friend ID next to the user_id of the currently logged in user.

The Query

To solve this problem, we can use the following query:

SELECT id FROM users 
WHERE id != $logged_in_user AND id NOT IN ( SELECT friend_id FROM friends WHERE user_id = $logged_in_user)

Let’s break down what’s happening here:

• We’re selecting only the id column from the users table.
• We’re excluding any rows where the id matches the current logged in user’s ID ($logged_in_user).
• We’re also excluding any rows where the id appears in the friend_id column of the friends table, but only for the specific row where the corresponding user_id matches the current logged in user.

This query is equivalent to finding all users who do not have a friend ID that matches the currently logged in user’s ID.

Why It Works

The reason this query works is because we’re using both an equality check (id != $logged_in_user) and a “not in” check (NOT IN). The NOT IN clause allows us to filter out rows where the id appears in the friend_id column for the current logged in user.

By combining these two conditions, we ensure that we’re only returning users who do not have any friendship relationships with the currently logged in user.

Example Use Cases

Here’s an example of how this query can be used in practice:

Suppose we have a web application that allows users to add friends. We want to display a list of all users who are not already friends with the current logged in user.

We can use the following MySQL query to achieve this:

SELECT u.id, u.username 
FROM users u 
LEFT JOIN friends f ON u.id = f.friend_id AND f.user_id = $logged_in_user 
WHERE f.id IS NULL AND u.id != $logged_in_user

This query joins the users table with the friends table on both sides of the friendship relationship. It then filters out any rows where there is a matching friend ID, leaving only users who are not already friends with the current logged in user.

Best Practices

Here are some best practices to keep in mind when working with relationships between users:

• Use meaningful column names and data types for your tables.
• Consider using indexes on columns used in JOIN operations.
• Regularly back up and synchronize your database to prevent data loss.
• Test your queries thoroughly before deploying them in production.

Conclusion

Managing friendships between users can be complex, but by breaking down the problem into smaller, more manageable parts, we can create efficient and effective solutions. By understanding how to use NOT IN clauses and LEFT JOINs, you’ll be better equipped to tackle similar challenges in your own development projects.

Last modified on 2023-08-26