CODEWITHSUNDEEP
mysqldatabase-designmetadatakey-value
JamesG
JamesG

Reputation: 2018

MySQL database design for custom meta key => value queries

I am making a product database for my website. I currently have:

Products Table: Product ID, Product name, Price

Product_meta Table: Pmeta_id, Product ID, metaKey, metaValue

I basically want it so I can query a product by specifying the meta key and either put a key=>value pair to it, or get the value by the key.

I believe Wordpress has a similar approach but am not quite sure how to go about making the functions to get the data. I assume it will involve table joins???

Any help would be appreciated.

Upvotes: 1

Views: 2131

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

It will invlovle JOIN's but more important is that, you will need to pivot all the rows so that you have those meta keys as columns rather than rows. Something like:

SELECT 
  p.productid, 
  p.productname, 
  p.price,
  MAX(CASE WHEN m.metaname = 'Type' THEN pm.metavalue END) AS 'Type',
  MAX(CASE WHEN m.metaname = 'Size' THEN pm.metavalue END) AS 'Size',
  MAX(CASE WHEN m.metaname = 'Quantity' THEN pm.metavalue END) AS 'Quantity'
FROM products p 
INNER JOIN product_meta pm ON p.productID = pm.productid
INNER JOIN metakeys m ON pm.metakey = m.metakey
GROUP BY p.productid, 
         p.productname, 
         p.price;

SQL Fiddle Demo

You can also do this dynamically for all the keys you have, instead of manually write them:

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(m.metaName = ''',
      m.metaName, ''', pm.metavalue, NULL)) AS ',
      m.metaName )
  ) INTO @sql
FROM products p 
INNER JOIN product_meta pm ON p.productID = pm.productid
INNER JOIN metakeys m ON pm.metakey = m.metakey
;



SET @sql = CONCAT('SELECT p.productid, p.productname,',
                  'p.price, ', @sql,
                  ' FROM products p  ',
                  ' INNER JOIN product_meta pm ON p.productID = pm.productid ',
                  ' INNER JOIN metakeys m ON pm.metakey = m.metakey ',
                  ' GROUP BY p.productid, p.productname, p.price; ');
PREPARE stmt 
FROM @sql; 

EXECUTE stmt;

Updated SQL Fiddle Demo

Upvotes: 1

AgeDeO
AgeDeO

Reputation: 3157

SELECT * FROM Product_meta AS M
LEFT OUTER JOIN Products AS P ON ( P.Product_ID = M.Product_ID )
WHERE M.metaKey = ...

Or turn it around if the Products table is leading:

SELECT * FROM Products AS P
LEFT OUTER JOIN Product_meta AS M ON ( P.Product_ID = M.Product_ID )
WHERE M.metaKey = ...

You can also change LEFT OUTER JOIN to INNER JOIN when you only want to display the records that are both in the Products table and in the Product_meta table

If you want to have more than one value in the WHERE clause then use this query:

... WHERE M.metaKey IN (..,..,..)

Make sure your data is comma seperated

Upvotes: 2

Related Questions