Right mySql command to get meta information?

Question

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     |me@me1.com|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     |me@me1.com|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

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, Aug 18 '11 at 21:18
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 http://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, Aug 19 '11 at 03:02
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, Aug 19 '11 at 03:14
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, Aug 19 '11 at 03:39
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, Aug 19 '11 at 03:51

Doug Kress · Accepted Answer · 2011-08-19T05:28:54.463

1

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;

edited Aug 19 '11 at 05:28

answered Aug 19 '11 at 03:33

Doug Kress

3,537
1
13
19

Thanks, Doug, it looks interesting but no luck for me. I'm getting an error related to user_id = u.id in the middle of your query and I can't seem to resolve it. Did you test this sql statement? Here's two tables and data you can use: http://pastebin.com/w0jkxnws – cwd Aug 19 '11 at 04:19
I tested it, but just on 3 fields. It's getting truncated - I'm working on a solution... – Doug Kress Aug 19 '11 at 05:00
the `set group_concat_max_len = 2048;` (added in the answer) fixed the problem with your dataset. – Doug Kress Aug 19 '11 at 05:08
That looks damn good! I'm impressed! On bigger tables I had to bump up the max_len, but that's ok with me. It might still need one more tweak, though. It is creating duplicate columns, probably based on the number of users: http://img696.imageshack.us/img696/3893/picturelu.png - Seems like we need one more condition in the WHERE clause? – cwd Aug 19 '11 at 05:17
I've just added the 'select distinct' subquery at the end of the statement - that should fix it. – Doug Kress Aug 19 '11 at 05:29
you, sir, are awesome. I am impressed. thank you. FYI for other users, on a big table I bumped up my group_concat_max_len to around 10000. If that number is not high enough you will get some error, although it might not be very descriptive. – cwd Aug 20 '11 at 17:22

score 0 · Answer 2 · answered Jul 06 '11 at 20:23

0

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

answered Jul 06 '11 at 20:23

Gedrox

3,592
1
21
29

yes, i think that's what i meant by building the sql statement manually with php. thanks :) – cwd Jul 06 '11 at 20:24

score 0 · Answer 3 · answered Jul 06 '11 at 20:35

0

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;

answered Jul 06 '11 at 20:35

Michael Mior

28,107
9
89
113

Thank you. The query "works", however it yields multiple rows per user. My objective is to have one line per user, with all meta data in it's own column. – cwd Jul 06 '11 at 20:39
Then you can add `GROUP_CONCAT(meta_value)` into your select and remove `meta_key` from the `group by`. if you want everything in a single column. – Michael Mior Jul 06 '11 at 20:41
yes, that's the second query example that i provided in the question. thank you. – cwd Jul 06 '11 at 21:09
So what you want is not ALL metadata in it's own column, but each piece of metadata in it's own column. You could achieve this with subqueries, but if possible, it's probably best to modify your application logic if you don't like the looks of your existing query. – Michael Mior Jul 06 '11 at 21:12

score 0 · Answer 4 · answered Jul 07 '11 at 08:06

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

Right mySql command to get meta information?

4 Answers4