title: codeigniter join only most recent row
body: <p>I am making a dating app similar to tindler, where users can like or dislike each other. If two users both like each other, they should be able to chat with each other. I have come up with the following query to handle pulling a list of users you can chat/have chats with already - the problem i'm having is i only want to pull the most recent chat message, solely to display a little blurb before you click into the chat itself. My query works, but it returns the oldest (lowest ID) chat record, instead of the newest. Order by does not seem to have an impact on returning the correct result.</p>
  
  <pre><code>$data = $this-&gt;db-&gt;select('users.id,display_name,city,state,gender,users_pictures.picture,users_chats.message')
                  -&gt;join('users_pictures','users_pictures.user_id=users.id')
                  -&gt;join('users_chats','users_chats.user_id=users.id OR users_chats.foreign_user_id=users.id','left outer')
                  -&gt;where('EXISTS (SELECT 1 FROM users_likes_dislikes ld WHERE (ld.foreign_user_id = '.$this-&gt;user_id.' AND ld.user_id=users.id AND ld.event_type=1) OR (SELECT 1 FROM users_likes_dislikes ld WHERE ld.foreign_user_id = users.id AND ld.user_id='.$this-&gt;user_id.' AND ld.event_type=1))', '', FALSE)
                  -&gt;where('NOT EXISTS (SELECT 1 FROM users_blocks ub WHERE (ub.foreign_user_id = users.id AND ub.user_id='.$this-&gt;user_id.') OR (SELECT 1 FROM users_blocks ub WHERE ub.foreign_user_id = '.$this-&gt;user_id.' AND ub.user_id=users.id))', '', FALSE)
                  -&gt;where('((users_chats.user_id='.$this-&gt;user_id.' OR users_chats.foreign_user_id='.$this-&gt;user_id.') OR (users_chats.user_id is NULL AND users_chats.foreign_user_id is NULL))')
                  -&gt;order_by('users_chats.id','DESC')
                  -&gt;group_by('users.id')              
                  -&gt;get('users')
                  -&gt;result_array();
  </code></pre>
  
  <p>Here is the current mysql table for users_chats:</p>
  
  <pre><code>id user_id foreign_user_id message created
  1 1 4 test 2013-05-22 15:42:44
  2 1 4 test2 2013-05-22 15:44:38
  </code></pre>
  
  <p>I assumed the order_by would ensure that the test2 message is what displayed.</p>
  
  <p>Here is sample output:</p>
  
  <pre><code>Array ( [0] =&gt; Array ( [id] =&gt; 4 [display_name] =&gt; testinguser [city] =&gt; west hills [state] =&gt; ca [gender] =&gt; 2 [picture] =&gt; testasdfasdf.jpg [message] =&gt; test ) )
  </code></pre>
  
  <p>Any help is much appreciated :)</p>
  
  <p>edit - the query itself (without group by, this works but i need it to group to the user.id so that i don't have multiple entries for the same user in the array): </p>
  
  <pre><code>SELECT
    `users`.`id`,
    `display_name`,
    `city`,
    `state`,
    `gender`,
    `users_pictures`.`picture`,
    `users_chats`.`message`
  FROM (`users`)
    JOIN `users_pictures`
      ON `users_pictures`.`user_id` = `users`.`id`
    JOIN `users_chats`
      ON `users_chats`.`user_id` = `users`.`id`
         OR users_chats.foreign_user_id = users.id
  WHERE EXISTS(SELECT
             1
           FROM users_likes_dislikes ld
           WHERE (ld.foreign_user_id = 1
              AND ld.user_id = users.id
              AND ld.event_type = 1)
            OR (SELECT
              1
                FROM users_likes_dislikes ld
                WHERE ld.foreign_user_id = users.id
                AND ld.user_id = 1
                AND ld.event_type = 1))
      AND NOT EXISTS(SELECT
               1
             FROM users_blocks ub
             WHERE (ub.foreign_user_id = users.id
                AND ub.user_id = 1)
              OR (SELECT
                    1
                  FROM users_blocks ub
                  WHERE ub.foreign_user_id = 1
                  AND ub.user_id = users.id))
      AND ((users_chats.user_id = 1
         OR users_chats.foreign_user_id = 1)
        OR (users_chats.user_id is NULL
            AND users_chats.foreign_user_id is NULL))
  ORDER BY `users_chats`.`created` DESC
  </code></pre>