How select last user information from user and usermeta tables?

Question

I'm use mysql and i have two tables in the form below:

User:
user_id - user_name - phone

UserMeta:
user_meta_id - user_id - meta_key - meta_value - meta_date

I have some records like the following:

user_id: 23  
meta_key: gender  
meta_value: male  
meta_date: 1534533650



user_id: 23  
meta_key: city  
meta_value: london  
meta_date: 1534533650



user_id: 23  
meta_key: name  
meta_value: jack  
meta_date: 1534533650  



user_id: 25  
meta_key: name  
meta_value: Jamie 
meta_date: 1534593881  

user_id: 25  
meta_key: gender  
meta_value: male 
meta_date: 1534593881  

user_id: 23  
meta_key: gender  
meta_value: female  
meta_date: 1534595971

user_id: 23  
meta_key: city  
meta_value: liverpool  
meta_date: 1534595971 

And ...

I need to get all user information (user_id = 23) with the latest changes registered, for example:

user_id: 23  
meta_key: name  
meta_value: Jamie  
meta_date: 1534533650 

user_id: 23  
meta_key: city  
meta_value: liverpool  
meta_date: 1534595971  

user_id: 23  
meta_key: gender  
meta_value: female  
meta_date: 1534595971  

The queries for this operation are complex and I'm confused, please help me,
I used this but did not get the right result:

    "SELECT kmu.*
            FROM user_meta kmu
            INNER JOIN
            (SELECT `meta_key`, `meta_value`, MAX(`meta_date`) AS MaxDateTime
            FROM user_meta
            GROUP BY meta_key) groupedtt
            ON user_id=:id
            AND kmu.meta_key = groupedtt.meta_key
            AND kmu.meta_date = groupedtt.MaxDateTime";

Answer 1

You can use the following:

SELECT `user`.*, user_meta.meta_key, user_meta.meta_value, user_meta.meta_date 
FROM `user` INNER JOIN (
    SELECT user_id, meta_key, MAX(meta_date) AS meta_date 
    FROM user_meta 
    GROUP BY user_id, meta_key
) meta_select ON `user`.user_id = meta_select.user_id 
INNER JOIN user_meta ON meta_select.meta_key = user_meta.meta_key 
    AND meta_select.user_id = user_meta.user_id 
    AND meta_select.meta_date = user_meta.meta_date
WHERE `user`.user_id = 23

demo: https://www.db-fiddle.com/f/euqvGNW6PguCG495Yi9dTa/1

Answer 2

While I strongly advice against such implementation, I'm not gonna try to change your mind. Although there are many ways to achieve this, I believe that the query below will have the least performance penalty (not using aggregates).

SELECT 
  user.*, meta.meta_key, meta.meta_value, meta.meta_date FROM user 
LEFT JOIN 
  (
    SELECT * FROM 
      (SELECT * FROM user_meta WHERE user_id = 23 ORDER BY meta_date DESC) sub 
    GROUP BY 
      sub.meta_key
  ) meta 
ON 
  user.user_id = meta.user_id
ORDER BY 
  meta.meta_key;

I formatted the query like this for readability. You might see that I'm using 2 nested sub queries. That requirement is coming from the fact that when you use GROUP BY clause the ORDER BY has no effect. Therefore first we select rows and order them by date desc, after that we group. This way it retains the sorting order.

Please see the working solution here, also please note that I added 2 INDEX CREATE statements to the sample database creation script in the left pane. These are bare minimum to ensure at least some adequate performance, as database grows.

Answer 3

I want to suggest a solution based on using of the ROW_NUMBER function. This solution has been broadly using in other databases. For example, look at here. But your version of MySQL doesn't support the ROW_NUMBER function and i used its emulation described here. To check my solution, click here.

SELECT
  *
FROM `user` AS u
LEFT JOIN (
  SELECT
    t.*
  FROM (
    SELECT
      @rn := IF(@uid = user_id AND @mk = meta_key, @rn + 1, 1) AS rn,
      @uid := user_id AS uid,
      @mk := meta_key AS mk,
      um.*
    FROM `user_meta` AS um
    CROSS JOIN (SELECT @uid := 0, @mk := 0, @rn := 0) AS i
    ORDER BY um.user_id, um.meta_key, um.meta_date DESC
  ) AS t
  WHERE t.rn = 1
) AS r ON u.user_id = r.user_id
WHERE u.user_id = 23