title: How to refine SQL query results to accommodate a view
body: <p>I am attempting to build a messaging application in Laravel 8 with two tables: <code>conversations</code> and <code>messages</code>. On the front end, the left column lists each conversation, and when clicked the content of the conversation displays. Everything works with one nagging exception, I cannot figure out how to query for conversations only and yet have the message data I need to work with.</p>
  <p><a href="https://i.stack.imgur.com/7JOf7.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7JOf7.png" alt="enter image description here" /></a></p>
  <p>I join the two tables to get the data I need, however, the query returns an element for each message. If I change the query to return only conversations where the users ID is sender or receiver, I get what I want, but not the data to populate the template.</p>
  <p>Here is the query and results to get the conversations a user has participated in as either sender or receiver:</p>
  <pre><code>    public function justConversations($id)
      {
          return DB::table('conversations')
          -&gt;where('conversations.party_one', '=', $id)
          -&gt;orWhere('conversations.party_two', '=', $id)
          -&gt;get(); 
      }
  </code></pre>
  <p><a href="https://i.stack.imgur.com/hC7xc.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hC7xc.png" alt="enter image description here" /></a></p>
  <p>The query with the data I need returns an element for each message, in this case two messages means the conversation displays two times versus what I want, which is once. Using distinct doesn't work because each message is different. Here's the query and the results I need to use:</p>
  <pre><code>    public function getAllMyConversations($id)
      {  
          return DB::table('conversations')
              -&gt;join('messages', 'messages.conversation_id', '=', 'conversations.id')
              -&gt;select(
                  'conversations.id AS message_id',
                  'conversations.party_one',
                  'conversations.party_two',
                  'conversations.party_one_name',
                  'conversations.party_two_name',
                  'conversations.created_at AS conversation_created_at',
                  'messages.conversation_id',
                  'messages.created_by',
                  'messages.sent_to',
                  'messages.created_by_name',
                  'messages.sent_to_name',
                  'messages.content',
                  'messages.read_at_created_by',
                  'messages.read_at_sent_to',
                  'messages.created_at AS message_created_at',
              )
              -&gt;where('conversations.party_one', '=', $id)
              -&gt;orWhere('conversations.party_two', '=', $id)
              -&gt;get(); 
      }
  </code></pre>
  <p><a href="https://i.stack.imgur.com/0C1m2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0C1m2.png" alt="enter image description here" /></a></p>
  <p>My approach might be incorrect. I'm at a dead-end. I hope this makes sense. I would appreciate any assistance in identifying my mistakes.</p>