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

2 Views

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”).

Read More

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. 🙂

Post Views: 2

Related posts

Leave a Reply

2 comments

  1. 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.

  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