title: Selecting Conversation From Database Based on Recipient
body: <p>I have been working on a PHP messaging system and I would like to implement two methods of accessing a particular conversation/thread.</p>
  
  <p>The first one is querying the ID of the conversation (<code>message/{numeric ID}</code>), which is pretty simple and straightforward. It is also the only way to query for a group message.</p>
  
  <p>The section method is querying for a personal '1-on-1' message thread (<code>message/{alphanumeric user ID}</code>). I will lay out the three tables used below.</p>
  
  <p>Table <code>conversations</code>:</p>
  
  <pre><code>id | locked | timestamp
  </code></pre>
  
  <p><code>timestamp</code> denotes the time of the last message in the conversation for the sake of having less queries when sorting.</p>
  
  <p>Table <code>participants</code>:</p>
  
  <pre><code>id | conversation | member | timestamp
  </code></pre>
  
  <p><code>conversation</code> is foreign ID key from <code>conversations</code>, <code>member</code> is the foreign key for table <code>members</code> and <code>timestamp</code> is the last activity of the user in the conversation for 'seen' functionality purposes.</p>
  
  <p>Table <code>messages</code>:</p>
  
  <pre><code>id | member | conversation | message | timestamp
  </code></pre>
  
  <p>The columns are pretty self-explanatory here.</p>
  
  <p>Now, I would like to fetch the ID of the conversation where I only have the person A and person B as participants based on their member ID's.</p>
  
  <p>I have thought about this for hours and no elegant solution comes to mind.</p>