How do I SELECT a "fake" column/value based on joined table values?

Question

Short Version:

I need to customize the result of a query, using one value as the column name, and another value as the column's value. It also needs to be automatic (not hard coded, hundreds of possible "meta keys").

Long Version:

I'm using Wordpress's "user meta" structure, where a single user has a user ID, and another table contains "meta-data" which is related to the user.

This meta data is arbitrary, and only has four columns:

meta_id, post_id, meta_key, meta_value.

I would like to do an SQL query for a certain user, and format the results so that all of the metadata is formatted as if they were rows, where the meta_key is a column name, and meta_value is the value for the column.

Example database:

-- wp_users
* ID    | username | email
35      | radgh    | radgh@example.org

-- wp_usermeta
* meta_id | user_id | meta_key   | meta_value
1         | 35      | first-name | Radley
2         | 35      | last-name  | Sustaire
3         | 35      | newsletter | on

My Query (I need the (???) portion figured out, which would generate the meta key/value pairs as fake columns)

 SELECT 
    `users`.ID as 'user_id',
    `users`.username as 'username',
    `users`.email as 'email',
    (???)

 FROM `wp_users` `users`
     INNER JOIN `wp_usermeta` `meta`
     ON `users`.ID = `meta`.user_id

 WHERE `wp_users`.ID = 35

Desired Outcome:

* user_id | username | email             | first-name | last-name | newsletter
35        | radgh    | radgh@example.org | Radley     | Sustaire  | on

In this similar question the selected answer uses hard coded fields. I would like to do this automatically. :)

Take the 'myfakecolumn' and also try to apply this logic. [http://stackoverflow.com/questions/15091330/sql-server-convert-columns-to-rows][1] [1]: http://stackoverflow.com/questions/15091330/sql-server-convert-columns-to-rows — RazorSky, Aug 06 '13 at 20:24

score 2 · Answer 1 · edited May 23 '17 at 10:27

2

It is called Entity–attribute–value model. Solution for dynamic columns (meta data) in MySQL via simple/complex query is AFAIK impossible to achive (like other great function like PIVOT MySQL is missing). The only way I think is static definition or MySQL function, which fetch from meta table possible attributes and build proper query.

Please look at What is best performance for Retrieving MySQL EAV results as Relational Table

Also read about EAV design (pros/cons) on stack Entity-Attribute-Value Table Design

edited May 23 '17 at 10:27

Community

1
1

answered Aug 06 '13 at 20:26

kwarunek

12,141
4
43
48

So much jargon to learn, it's no wonder I had so much trouble trying to find similar questions! I'll do some research about this, thank you! Since it may be impossible, perhaps doing a separate query just to find each meta key and then using PHP to format it in a "hard-coded" layout would be the way to go. – Radley Sustaire Aug 06 '13 at 20:30

score 2 · Accepted Answer · answered Aug 06 '13 at 20:37

MySQL has no pivot function. If you know already all of the possible values of meta_key, you could use a hard-coded query like this:

SELECT
  wp_users.*,
  MAX(CASE WHEN `meta_key`='first-name' THEN meta_value END) AS `first-name`,
  MAX(CASE WHEN `meta_key`='last-name' THEN meta_value END) AS `last-name`,
  ....
FROM
  wp_users INNER JOIN wp_usermeta
  ON wp_users.ID=wp_usermeta.user_id
GROUP BY
  wp_users.ID, wp_users.username, wp_users.email;

Or you could create your SQL query dynamically, using a prepared statement, like this:

SELECT
  CONCAT(
    'SELECT wp_users.*,',
    GROUP_CONCAT(
      CONCAT(
        'MAX(CASE WHEN `meta_key`=\'',
        meta_key,
        '\' THEN meta_value END) AS `',
        meta_key,
        '`')),
    ' FROM wp_users INNER JOIN wp_usermeta ON wp_users.ID=wp_usermeta.user_id',
    ' GROUP BY wp_users.ID, wp_users.username, wp_users.email')
FROM
  (SELECT DISTINCT meta_key FROM wp_usermeta) s
INTO @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;

Please see fiddle here.

I gave it a thought and could use two queries, one which gathers all possible meta-keys, then use PHP to build the second query like your example #1. However, I just played with #2 and created a new row, and it seems to work just like I wanted to begin with. Although it is a bit hard to read ;). Would there be a major benefit to option #1 versus #2? I suspect #2 will be making many SELECT queries, but performance is not required here - this script won't be used regularly. — Radley Sustaire, Aug 06 '13 at 20:47
I couldn't get the second option to work with HeidiSQL (not that the application matters), on MySQL 5.1.54. No need to debug it though, as I ended up going with option #1 using my idea from the last comment. In fact, the plugin (for wordpress) which stored these fields had a global variable with all the field names, so in the end I didn't even need an extra query, and don't have to worry about all the unecessary fields - only what is currently available. In the end, I think this was the best option anyway. Thank you for the sample queries! — Radley Sustaire, Aug 07 '13 at 16:51

How do I SELECT a "fake" column/value based on joined table values?

2 Answers2