MySQL Join meta table (WordPress style table structure)

Question

Maybe this is pretty basic and that's the reason why I haven't found anything for this…

Here is my table structure:

object

id | type    | …
1  | brand   | …
2  | project | …
3  | brand   | …

meta

id | object | name  | value
1  | 1      | name  | adidas
2  | 1      | color | blue
3  | 3      | name  | telekom
4  | 3      | color | pink

I want to fetch the data similar to:

SELECT o.*, EACH( m.value as m.name ) FROM object o LEFT JOIN meta m ON m.object = o.id GROUP BY id

which should result in:

id | type  | … | name    | color
1  | brand | … | adidas  | blue
2  | brand | … | telekom | pink

Any ideas? This have to be dynamically because the amount of meta rows and their keys can be different.

I think this is pretty close to the table structure of WordPress (posts & postmeta) … do they fetch their meta on the initial query or do they fire a second one for each result selecting the meta?

PIVOT is the key word here. Possible duplicate of [MySQL pivot table query with dynamic columns](https://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns) — Juan Carlos Oropeza, Aug 07 '19 at 14:13

Juan Carlos Oropeza · Answer 1 · 2019-08-08T13:54:36.787

SQL DEMO

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(m.name = ''',
      name,
      ''', m.value, NULL)) AS ',
      name
    )
  ) INTO @sql
FROM meta m;

@sql

MAX(IF(m.name = 'name', m.value, NULL)) AS name,
MAX(IF(m.name = 'color', m.value, NULL)) AS color

.

SET @sql = CONCAT('SELECT o.`id`
                    , o.`type`
                    , o.`description`, ', @sql, ' 
                   FROM object o
                   LEFT JOIN meta AS m
                    ON o.`id` = m.`object`
                   GROUP BY o.id');

@sql

SELECT o.`id`
     , o.`type`
     , o.`description`
     , MAX(IF(m.name = 'name', m.value, NULL)) AS name
     , MAX(IF(m.name = 'color', m.value, NULL)) AS color 
FROM object o
LEFT JOIN meta AS m
  ON o.`id` = m.`object`
GROUP BY o.id

.

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

OUTPUT

+----+---------+-------------+---------+-------+
| id |  type   | description |  name   | color |
+----+---------+-------------+---------+-------+
|  1 | brand   | a           | adidas  | blue  |
|  2 | project | b           |         |       |
|  3 | brand   | c           | telekom | pink  |
+----+---------+-------------+---------+-------+

Dome looks great, but how do you execute that in PHP + MySQL? — GDY, Aug 08 '19 at 06:08
My suggestion create a store proc in MySQL and call it from PHP — Juan Carlos Oropeza, Aug 08 '19 at 07:31

ScaisEdge · Answer 2 · 2019-08-07T14:21:12.950

0

You could try using two time the join on meta (one for each attribute in want in a separated column)

select o.type, m1.value name, m2.value color
from object  o
inner join  meta m1 on m1.object = o.id and m1.name = 'name'
inner join  meta m2 on m2.object = o.id and m2.name = 'color'
where o.type='brand'

butdo the fact mysql have not proper function for pivot table if you need dinamic query you should take a look at dinamic sql

edited Aug 07 '19 at 14:21

answered Aug 07 '19 at 14:18

ScaisEdge

131,976
10
91
107

OP says need dynamic query – Juan Carlos Oropeza Aug 07 '19 at 14:20
@scaisEdge thanks but i've found some example like yours … problem is that you have hard code these fields which is no solution for my scenario – GDY Aug 08 '19 at 05:50

MySQL Join meta table (WordPress style table structure)

2 Answers2