Databases
StackOverflow2013
Postsdbo
POCreating a threaded private messaging system like facebook and gmail
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCreating a threaded private messaging system like facebook and gmail
    primarykey
    data
    text
    <p>I am creating a threaded message system much like gmail and facebook where the inbox lists the most recent threads displaying the subject, the sender's name, and time-stamp of the most recent message. </p> <p>Here's How my tables are set up:</p> <pre><code>users: user_id user_name thread: thread_id title to_id to_keep to_read from_id from_keep date message: message_id thread_id to_id from_id message_text date </code></pre> <p>What I'm doing right now is when a user creates a new message, it creates a new thread in the thread table and then a new message in the message table and if a user responds to a thread, it duplicates the current thread in the thread table except it swaps the <code>to_id</code> and <code>from_id</code> and then creates a new message based on that.</p> <p>Also, for the inbox view, I'm able to just query all threads based on a <code>user_id</code>. so something like <code>SELECT * FROM thread WHERE to_id = 2 and to_keep = TRUE ORDER BY date DESC</code> or if I want to view messages in the outbox it would be something like <code>SELECT * FROM thread WHERE from_id = 2 and from_keep = TRUE ORDER BY date DESC</code>.</p> <p>If a user opens a thread when there's a new message, then to_read is updated to true <code>UPDATE thread SET to_read = TRUE WHERE thread_id = 4</code>.</p> <p>I feel like I'm over complicating this process and that there should be a better way to do this. </p> <p>Any help or ideas would be appreciated.</p> <p>This way let's me just select everything from the thread table and then do a join with the user table to display everything I need. However I feel like there should be a better way to do this.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USbigmike7801
    1. USbigmike7801
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCreating a threaded private messaging system like facebook and gmail
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POCreating a threaded private messaging system like facebook and gmail
      1. USAlfred
      1. VTFavorite
    3. VO
      singulars
      1. POCreating a threaded private messaging system like facebook and gmail
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhy does the thread need a to_id and a from_id? The message is the thing which binds together a sender and a receiver. The thread is just the container for the messages. Or am I misunderstanding?
      singulars
      1. POCreating a threaded private messaging system like facebook and gmail
      1. UStom redfern
    2. CONo, you're correct. I was just having a difficult time with my queries selecting all threads that should be in the inbox and just the info from just the most recent message in the thread. Does that make sense?
      singulars
      1. POCreating a threaded private messaging system like facebook and gmail
      1. USbigmike7801
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload