title: Retrieving data from PostgreSQL DB in a more efficient way
body: <p>I'm developing a real-time chat app using PostgreSQL and I'm having the following issue:</p>
  <p>When a user logs in, I need to fetch all the users that are not the logged-in user, in order to display them on the sidebar of the app.</p>
  <p>Below each user should be displayed the latest message that was sent either by the logged-in user or by the other user.</p>
  <p>I'm trying to execute an efficient query in order to retrieve all the users with their latest message at once but with no success.</p>
  <p>Here are my tabels:</p>
  <p><a href="https://i.stack.imgur.com/JUsGv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JUsGv.png" alt="enter image description here" /></a>
  <a href="https://i.stack.imgur.com/VZvc5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VZvc5.png" alt="enter image description here" /></a></p>
  <p>I tried at first to do something like that:</p>
  <pre><code>SELECT users.id, users.first_name, users.last_name, users.image, messages.sender_id, messages.recipient_id, messages.content
  FROM users LEFT JOIN messages on users.id = messages.sender_id OR users.id = messages.recipient_id
  WHERE (messages.sender_id = 1 OR messages.recipient_id = 1) AND users.id != 1
  GROUP BY users.id
  ORDER BY messages.created_at DESC;
  </code></pre>
  <p>And I got this error:</p>
  <p><a href="https://i.stack.imgur.com/Fuyk3.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Fuyk3.png" alt="enter image description here" /></a></p>
  <ul>
  <li>&quot;1&quot; refers to the logged user id</li>
  </ul>
  <p>My temporary solution is to fetch all the users from the db, mapping over them on the server and executing another query which sends back the latest message between the users using - ORDER BY created_at DESC LIMIT 1.</p>
  <p>I'm sure there are more efficient ways, and I would appreciate any help!</p>