How can I select * WHERE & receive other results connected to the found primary keys?

Question

So I've got the following tables setup:

thread:

• id
• created_at

thread_users:

• id
• thread_id -> FK thread
• user_id -> FK users (the user who is in the thread)
• joined_at

users:

• id
• name
• etc...other relevant details for user

Current functionality: When a user clicks on another user (e.g there FRIEND in the app), it will open the message thread between the 2 users (it should grab the msgs between the 2 users). To determine WHICH thread to use (we need to find the thread_id between the 2 users), we must query the thread_users table using the HTTP_REQUEST_USER (user who sent the GET request) & and the user_id PARAM passed in the http GET request.

Once I do this: select * from thread_users where user_id = HTTP_REQUEST_USER_ID OR user_id = GET_REQUEST_PARAM_USER_ID

It will return a bunch of threads that the users are in, I then group the returned results using the lodash groupBy method: (which basically MOVES all the results into there corresponding thread objects - E.g 6: [ { user1 }, { user2 } ] - This represents the thread_id 6)

const groupedThreads = _.groupBy(foundThreadUsers, 'thread_id');

From here I loop through each of the found threads (groupedThreads will contain objects THAT contain the arrays of user in each of the threads)

Object.keys(groupedThreads).forEach((key) => {
      const thr = groupedThreads[key].filter(tu => tu.user_id === currentUserId || tu.user_id === userId);
      console.log(thr, 'FOUND THREAD inside loop after filter');
      /**
       * @TODO add something here which will take into account pluralbot
       */
      if (thr.length === 2) {
        foundThread = thr[0];
      }
    });

However, this bit of logic doesn't work:

if (thr.length === 2) {
   foundThread = thr[0];
}

Because the select * from ... (query listed above ^^), only returns the thread_users that match the user ids passed into the where user_id = x OR user_id = y.

I want to be able to select * from thread_users where user_id = HTTP_REQUEST_USER_ID OR user_id = GET_REQUEST_PARAM_USER_ID BUT also return the other users WHO match the found thread_users.thread_id. Is this possible? Or is there another way I could find the thread between the 2 users?

Answer 1

This is the trivial solution using a normal relational data model. (you could combine the two EXISTS() conditions) :

---

SELECT * 
FROM thread th
WHERE EXISTS (
        SELECT * 
        FROM user_thread ut
        WHERE ut.thread_id = th.id
        AND ut.user_id = ***user1***
        )
AND EXISTS (
        SELECT *
        FROM user_thread ut
        WHERE ut.thread_id = th.id
        AND ut.user_id = ***user2***
        )
        ;

Answer 2

This will get you the answer you're looking for:

SELECT DISTINCT  -- Use DISTINCT to avoid getting duplicates for inverse pairing
  T.id
FROM thread T
  INNER JOIN thread_users U1
    ON T.id = U1.thread_id
  INNER JOIN thread_users U2
    ON T.id = U2.thread_id
WHERE U1.user_id = @FirstUserID
  AND U2.user_id = @SecondUserID

Answer 3

Okay so what I ended up doing was adding a new field to the thread table named: thread_name. The thread name consists of the 2 user ids joined together, e.g: 445-123. The reason for this is so I can easily query for a thread between 2 users! I'm not sure if I will do this for group messages though (we will see when the time comes).

Example usage:

const name = [http_req_userId, otherUserId].sort().join('-'); // Returns something like 123-323
select * from threads where thread_name = name;

Note that I'm sorting the 2 IDS before joining them together so I don't create duplicate threads between users.

I got the idea for this off this SO question (see comments of below answer): thread messaging system database schema design

EDIT

Do not use this approach - I will leave this here as a reference for what NOT to do.