MySQL Select Data From two tables and generate column name dynamically

1: Product info (corewp_product) 2: Product Metadata (corewp_productmeta)

I want to select (assume the user is searching) by price, color, size e.t.c metadata depending on the search parameter and metadata available.

Eg. search might be

where (color=red and price between 100 and 500)

Since metadata is dynamically added I don't want to create a new column for each metadata. Some of the products are in group (eg. Sneaker might be in red,blue with various prices)

My Tables are like this:

1. corewp_product

2. corewp_productmeta

Problem:

Selecting products with color = red and price between 0 and 50 or with other metadata.

Approach 1- using join: I have tried to do it this way

  SELECT corewp_product.id, corewp_product.idname, corewp_product.title, P.amount, C.color FROM corewp_product JOIN ( SELECT `postid_field` as priceId, `meta_value` as amount, `group_id` as ggroup FROM `corewp_productmeta` WHERE (`meta_name` = 'price' AND `meta_value` BETWEEN 10 AND 50)) AS P JOIN (SELECT `postid_field` as colorId, `meta_value` as color, `group_id` as ggroup FROM `corewp_productmeta` WHERE (`meta_name` = 'color' AND `meta_value` = 'red') GROUP BY `group_id`,`postid_field`) AS C ON p.ggroup = C.ggroup WHERE corewp_product.idname = P.priceId AND corewp_product.idname = C.colorId

But the problem with the code above is what happen when a new meta data is added e.g: brand name

and the new search has to include brand name color = red and brand = nike and price between 0 and 50, I will have to alter the query above which is something am looking to avoid.

Approach 2- using view:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(pm.meta_name = ''',
      meta_name,
      ''', pm.meta_value, NULL)) AS ',
      meta_name
    )
  ) INTO @sql
FROM corewp_productmeta;

SET @sql = CONCAT('SELECT p.idname , p.title, ', @sql, ' FROM corewp_product p LEFT JOIN corewp_productmeta AS pm ON p.idname = pm.postid_field GROUP BY pm.group_id,p.idname,p.title');
SET @qrys = CONCAT('CREATE OR REPLACE VIEW meta_view AS ',@sql); 
PREPARE stmt FROM @qrys;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The approach works as expected but now I have to get all the data from the view table which also I want to avoid now an issue comes when a new meta data is added e.g. brand same issue repeat.

It will be great if I could be able to query like select... where brand=xx and color=aa then results would come with column brand and name if brand doesn't exist then brand column returned as null or result not found same with other dynamic values passed in a query

Is there any way you can help me guys? I will appriciate.

N.B: this query will also include pagination limit (0,10) once full system is deployed.

SQL FORMAT

 CREATE TABLE `corewp_product` (
  `id` int(11) NOT NULL  PRIMARY KEY AUTO_INCREMENT,
  `idname` varchar(20) NOT NULL,
  `title` varchar(60) NOT NULL,
  `category` int(11) NOT NULL,
  `price` double(20,2) NOT NULL,
  `type` varchar(20) NOT NULL,
  `meta` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `corewp_product` (`id`, `idname`, `title`, `category`, `price`, `type`, `meta`) VALUES
(1, 'A1', 'Sneakers', 2, 0.00, 'grouped', 0),
(2, 'A2', 'Branded Shirts for sale', 1, 0.00, 'grouped', 0),
(3, 'A3', 'Long Sleeve Shirts', 1, 0.00, 'grouped', 0);

CREATE TABLE `corewp_productmeta` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `postid_field` varchar(5) NOT NULL,
  `group_id` varchar(5) NOT NULL,
  `meta_name` varchar(50) NOT NULL,
  `meta_value` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `corewp_productmeta` (`id`, `postid_field`, `group_id`, `meta_name`, `meta_value`) VALUES
(1, 'A1', 'G1', 'color', 'red'),
(2, 'A1', 'G1', 'size', 'EU40'),
(3, 'A1', 'G1', 'price', '28'),
(4, 'A1', 'G2', 'size', 'EU41'),
(5, 'A1', 'G2', 'color', 'black'),
(6, 'A1', 'G2', 'price', '30'),
(7, 'A1', 'G3', 'color', 'red'),
(8, 'A1', 'G3', 'size', 'E40'),
(9, 'A1', 'G3', 'price', '50'),
(10, 'A2', 'G1', 'color', 'any'),
(11, 'A2', 'G1', 'size', 'L'),
(12, 'A2', 'G1', 'price', '60'),
(13, 'A3', 'G1', 'color', 'red'),
(14, 'A3', 'G1', 'price', '30');