How to Retrieve All Relations That the Current User is Involved In Using ASP.NET SQL Server

How to Select All Relations That Current User is Involved In?

As a technical blogger, I’ll dive into the world of ASP.NET SQL Server and explore how to select all relations that the current user is involved in.

Background

In this example, we have two tables: User and UserRelationship. The User table stores information about each user, including their username, first name, last name, date of birth, city, address, phone number, email, rank, and profile image. The UserRelationship table stores relationships between users, such as friendships.

The relationship is established by the userFirstID and userSecondID columns in the UserRelationship table, which point to the respective user IDs in the User table. We also have a relationshipType column that indicates the type of relationship (e.g., ‘friends’).

Table Definitions

Here are the create statements for the tables:

CREATE TABLE [dbo].[User]
(
    [userID]      INT            IDENTITY (1, 1) NOT NULL,
    [username]    NVARCHAR (50)  NOT NULL,
    [firstName]   NVARCHAR (50)  NULL,
    [lastName]    NVARCHAR (50)  NULL,
    [dateOfBirth] DATE           NULL,
    [city]        NVARCHAR (50)  NULL,
    [address]     NVARCHAR (50)  NULL,
    [phoneNumber] INT            NULL,
    [email]       NVARCHAR (50)  NULL,
    [rank]        NVARCHAR (50)  NULL,
    [profilImage] NVARCHAR (255) NULL,

    PRIMARY KEY CLUSTERED ([userID] ASC),
    CONSTRAINT [AK_User_username] 
        UNIQUE NONCLUSTERED ([username] ASC)
);

CREATE TABLE [dbo].[UserRelationship]
(
    [userFirstID]      INT           NOT NULL,
    [userSecondID]     INT           NOT NULL,
    [initiatedBy]      NVARCHAR (50) NOT NULL,
    [relationshipType] NVARCHAR (50) NOT NULL,

    CONSTRAINT [PK_UserRelationship] 
        PRIMARY KEY CLUSTERED ([userFirstID] ASC, [userSecondID] ASC),
    CONSTRAINT [FK_UserRelationship_userFirstID] 
        FOREIGN KEY ([userFirstID]) REFERENCES [dbo].[User] ([userID]),
    CONSTRAINT [FK_UserRelationship_initiatedBy] 
        FOREIGN KEY ([initiatedBy]) REFERENCES [dbo].[User] ([username]),
    CONSTRAINT [CK_UserRelationship_relationshipType] 
        CHECK ([relationshipType]='pending' OR [relationshipType]='friends')
);

The Initial Query

The user has posted a query that retrieves all relations where the current user is involved:

SELECT 
    UserRelationship.relationshipType, 
    UserRelationship.userFirstID, UserRelationship.userSecondID, 
    [User].username, [User].firstName, [User].lastName
FROM  
    [User] 
INNER JOIN
    UserRelationship ON [User].userID = UserRelationship.userFirstID 
                     AND UserRelationship.relationshipType = 'friends' 
                     OR [User].userID = UserRelationship.userSecondID 
                     AND UserRelationship.relationshipType = 'friends'
WHERE 
    ([User].userID = @userID)

This query uses inner joins to connect the User table with the UserRelationship table. However, it only returns relations where the current user is involved as either the first or second party in the relationship.

The Correct Query

To retrieve all relations that the current user is involved in, we need to modify the query to join the User table twice, once for each user ID in the UserRelationship table. We can then use a CASE statement to determine which user name to return:

SELECT 
    UserRelationship.relationshipType,
    UserRelationship.userFirstID,
    UserRelationship.userSecondID,
    [User].username AS userName,
    [User].firstName AS firstName,
    [User].lastName AS lastName,
    CASE [User].[UserID]
        WHEN OtherUser1.userId THEN OtherUser2.userName
        WHEN OtherUser2.userId THEN OtherUser1.userName
    END AS otherUserName,
    CASE [User].[UserID]
        WHEN OtherUser1.userId THEN OtherUser2.firstName
        WHEN OtherUser2.userId THEN OtherUser1.firstName
    END AS otherFirstName,
    CASE [User].[UserID]
        WHEN OtherUser1.userId THEN OtherUser2.lastName
        WHEN OtherUser2.userId THEN OtherUser1.lastName
    END AS otherLastName
FROM 
    [User] 
INNER JOIN UserRelationship ON [User].userID IN (UserRelationship.userFirstID, UserRelationship.userSecondID) AND UserRelationship.relationshipType = 'friends'
INNER JOIN [User] OtherUser1 ON OtherUser1.userID = UserRelationship.userFirstID
INNER JOIN [User] OtherUser2 ON OtherUser2.userID = UserRelationship.userSecondID
WHERE 
    ([User].userID = @userID)

This query uses inner joins to connect the User table with the UserRelationship table twice, once for each user ID in the relationship. The CASE statement determines which user name to return based on whether the current user is the first or second party in the relationship.

Explanation

The key to this query is the use of inner joins and a CASE statement to determine which user name to return. We join the User table with the UserRelationship table twice, once for each user ID in the relationship. The CASE statement checks whether the current user is the first or second party in the relationship and returns the corresponding user name.

Additional Considerations

There are a few additional considerations to keep in mind when writing this query:

We assume that a user cannot be friends with themselves, which requires an extension of the CASE statement for the case where all three IDs are equal.
We also assume that the relationship type is always either ‘pending’ or ‘friends’. If there are other relationship types, we would need to modify the query accordingly.

Example Use Case

This query can be used in a variety of scenarios where you need to retrieve relationships between users. For example:

A social media platform might use this query to display a user’s friends list.
An online community platform might use this query to display a user’s relationships with other members.

I hope this helps clarify how to select all relations that the current user is involved in using ASP.NET SQL Server!

Last modified on 2023-07-30