0

I have a simple message schema where a "thread" ties 2 or more users to a stream of messages. Each message belongs to a single thread. It works just like SMS messages, or Facebook messages.

Given a string (representing a name or partial name of a user), I need a query that will find all threads that match where:

  1. the current user (userID) is a member of the thread
  2. the name or partial name of a user is also a member of the thread

Here are my tables:

MessageThreads:
threadID
lastUpdated

MessageThreadUsers:
threadFK
userFK

Users
userID
userFirstName
userLastName
userFullName

This query gets all threads that the current user belongs to:

SELECT DISTINCT threadFK FROM MessageThreadUsers
WHERE userFK = 'usr_developer'

But how would I join each thread with all users in the thread (not including the current user) that match by name or partial name?

Redtopia
  • 4,947
  • 7
  • 45
  • 68

1 Answers1

1

This should show you the other users of all threads that the 'usr_developer' key is part of.

    SELECT MT.ThreadId, U.userID, U.userFullName
      FROM MessageThreads MT 

INNER JOIN MessageThreadUsers MTCU on (MTCU.threadFK = MT.threadID)
INNER JOIN Users CU on (MTCU.userFK = CU.userID and CU.userID = 'usr_developer')

INNER JOIN MessageThreadUsers MTU on (MTU.threadFK = MT.threadID)
INNER JOIN Users U on (MTU.userFK = U.userID and U.userID <> 'usr_developer') 

     WHERE U.UserFullName like '%John%' -- Do your filters here

This joins against the tables twice, first, we join to the users table to only pull back the set of threads that have a user with the id of 'usr_developer'. Then we join the resulting threads against the users table again, this time where the user id is not usr_developer. Finally we can filter the result set by the name.

N West
  • 6,768
  • 25
  • 40
  • Oops just reread, I am fixing this to include the current user must be a member of the thread. – N West Mar 29 '12 at 18:40
  • There's an error on Line 5: MTC.userFK cannot be bound. What should this be? – Redtopia Mar 29 '12 at 19:09
  • This is looking like it will work. One enhancement I need to make is to get the most recent message and the sender of that message for the thread. I can get the message easily (already done that) by comparing MessageThreads.lastUpdated with Messages.messageDate. But I need to join that with the Users table again... I need to get user who wrote that message (Messages.senderFK). Make sense? – Redtopia Mar 29 '12 at 19:35
  • Great! Also, I would recommend using integers as surrogate keys on the Users and MessageThreads tables, instead of characters. Then your joins will perform better. – N West Mar 29 '12 at 20:33
  • I am using UUIDs for IDs, so they're up to 35 characters. I realize that using ints would be better. I'm currently building a site that needs to support millions of users... do you think I'm really going to need to make that change? I suppose I could migrate the system by creating a bigint ID for all objects that use UUIDs, and then change my queries down the road. – Redtopia Mar 29 '12 at 22:59
  • UUIDs are very large. Even if you have 10M users, you would only have 10M user keys (could be some savings in space there). A UUID is always 16 bytes - many DBMS can store numbers in a compressed format (anywhere from 3-8 bytes). – N West Mar 30 '12 at 13:33