How can I join in data from a key : value meta table in mysql?

Question

Say I have three tables in my database:

CREATE TABLE `users` (
`user_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR(16) NOT NULL
);

CREATE TABLE `users_meta` (
`meta_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`user_id` INT NOT NULL ,
`key` VARCHAR(200) NOT NULL ,
`value` TEXT NOT NULL
);

CREATE TABLE `posts` (
`post_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`user_id` INT NOT NULL ,
`content` TEXT NOT NULL
);

The table users_meta is just a key-value store of information about users, such that we can add any piece of information we want.

Say I added a key => value pair to the users_meta table for each user where the key was "age", and the value was a number representing their age.

Given this set of circumstances, what's the best way to select the first 10 posts ordered by user age?

Answer 1

If you order by user age only, you will select 10 posts of the same user (the youngest one).

I would suggest to denormalize and store age in users table directly.

Answer 2

Agree with @KOHb, but if that's exactly what you want, here is the query:

SELECT TOP 10 p.id, p.content
FROM users u JOIN users_meta um ON (u.user_id = um.user_id) 
             JOIN posts p ON (p.user_id = u.user_id)
WHERE um.key = 'age'
ORDER BY um.value

Answer 3

I like putting the condition of the join in the join itself to be clear that I want a limited join:

SELECT p.post_id, p.content
FROM users u 
INNER JOIN users_meta um 
    ON (u.user_id = um.user_id) AND um.key = 'age'
INNER JOIN posts p 
    ON (p.user_id = u.user_id)
ORDER BY um.value
limit 10