Right mySql command to get meta information?

4
votes

Wordpress is a good example of a web application that uses a table for user info, and then a meta lookup table for user data. The only problem is that the only way I know of to get a complete list of meta information for a list of users is to build the sql statement "manually" - either hard coded or with the help of PHP.

The user table looks something like this:

wp_users table

ID|user_login|user_email|user_pass|date_registered
==================================================
 1|   me     |[email protected]|f239j283r|   2011-01-01

wp_usermeta table

umeta_id|user_id|meta_key|meta_value
====================================
   1    |   1   | phone  | 123-4567
   1    |   1   | fname  | john
   1    |   1   | lname  | doe

I know I can do something like this (manually or with php) to achieve the result of what I want:

select *
from wp_users
left join wp_usermeta as phone on (ID = user_id) AND (meta_key = phone)
left join wp_usermeta as fname on (ID = user_id) AND (meta_key = fname)
left join wp_usermeta as lname on (ID = user_id) AND (meta_key = lname)

that yields something like this:

ID|user_login|user_email|user_pass|date_registered|phone   |fname|lname
=================================================================+++===
 1|   me     |[email protected]|f239j283r|   2011-01-01  |123-4567|john |doe

I know mySql also has the GROUP_CONCAT thing, which is why I feel like there is a better way. That would look something like this:

select *, group_concat(meta_value) as all_meta
from wp_users
left join wp_usermeta on ID = user_id
group by wp_users.ID

So is there a way to get the result similar to that from the first sql statement with a more dynamic sql statement like the second one?

Edit

Doug has proposed an interesting solution, possibly using information_schema. I was having trouble getting that to work so I've posted a dump of the two tables for anyone who wants to test their SQL :) http://pastebin.com/w0jkxnws

4
phpmysqlsql
I'm unclear as to your goal, based in the comments on the answers listed. Andriy M's solution is elagent, but requires knowing the column names. Using group_concat, as you suggested, gets you all the information dynamically. there's no real way to dynamically flip a table sideways (AFAIK), so you've listed pretty much all of the options. - Doug Kress
I've tried to be as clear and concise as possible in stating my goal. It just comes down to the fact that a meta lookup table is the most scalable way to contain abstract information for a list of users (or other items); However getting all data for the users in a tabular format seems to require a lot of steps. I had given up on it after researching it, but I keep seeing very smart ways of doing things on S.O., (like stackoverflow.com/questions/9122) and so I've posted this question hoping there is a more dynamic way than hard coding, or using PHP and multiple queries. Maybe there isn't - cwd
Ah. The referenced answer would work, but pulling from information_schema for every query does have a hefty overhead price. I would recommend doing that in code, and caching the result (so that you don't have to re-do it all the time), but I'd be happy to give that kind of answer. - Doug Kress
I don't agree that using information_schema would necessarily have a 'hefty overhead price' - we could run query profiling to find out. I would welcome an answer that uses information_schema to achieve the desired result. That is more the direction I was hoping for compared to the answers that have already been posted (I was already aware of those methods). Thanks Doug! - cwd
It certainly depends on what you're doing, and how complex the task is and how complex your schema is. Counts are bad and all, but in my big database, running select count(*) from information_schema.columns; took 7.6 seconds for 15,000 records. - Doug Kress

4 Answers

1
votes

Is this what you're looking for? It's still 3 statements, but, contrary to my previous statement to the contrary, there shouldn't be much prep cost.

set group_concat_max_len = 2048;
SELECT CONCAT('SELECT u.id, u.user_login, ', GROUP_CONCAT(concat('
    (SELECT meta_value FROM wp_usermeta WHERE user_id = u.id AND meta_key = "', um.meta_key, '") `', um.meta_key, '`') SEPARATOR ", "), '
    FROM wp_users u ') FROM (SELECT DISTINCT meta_key FROM wp_usermeta) um INTO @sql;

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
0
votes

Usually this is done by running 2 queries against the database – first to fetch user record, second for properties.

0
votes

Try this

select *
from wp_users
left join wp_usermeta as fname on (ID = user_id)
where meta_key in ('fname','lname','phone')
group by ID, meta_key;
0
votes

You could try something like this:

SELECT
  u.*,
  MIN(CASE m.meta_key WHEN 'phone' THEN m.meta_value END) AS phone,
  MIN(CASE m.meta_key WHEN 'fname' THEN m.meta_value END) AS fname,
  MIN(CASE m.meta_key WHEN 'lname' THEN m.meta_value END) AS lname
FROM wp_users u
  LEFT JOIN wp_usermeta m ON u.ID = m.user_id
    AND m.meta_key IN ('phone', 'fname', 'lname')
GROUP BY u.ID