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

id	idname	title	category	price	type	meta
1	A1	Sneakers	2	0	grouped	0
2	A2	Branded Shirts for sale	1	0	grouped	0
3	A3	Long Sleeve Shirts	1	0	grouped	0

2. corewp_productmeta

id	postid_field	group_id	meta_name	meta_value
1	A1	G1	color	red
2	A1	G1	size	EU40
3	A1	G1	price	28
4	A1	G2	color	black
5	A1	G2	size	EU41
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

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

id	postid_field	group_id	meta_name	meta_value
15	A1	G1	brand	nike

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');

CodePudding user response：

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

SELECT pr.*
FROM corewp_product pr
JOIN corewp_productmeta pm ON pr.idname = pm.postid_field
WHERE (pm.meta_name = 'color' AND meta_value = 'red')
   OR (pm.meta_name = 'price' AND meta_value   0 BETWEEN 100 AND 500)
GROUP BY pr.id
HAVING COUNT(*) = 2;

DEMO

Some explanations.

We select the metadata rows which matches any of the criteria. Then we group by the product and count the amount of matched meta values for it. Finally we return only those products which have the matched amount equal to total one.

This query does not need in dynamic SQL. You must only put correct values into the conditions.

Pay attention to this: meta_value 0 BETWEEN 100 AND 500. The addition 0 performs implicit data casting to numeric (of course we can use explicit CAST(meta_value AS UNSIGNED)). This allows make numeric comparing context. Without this addition the comparing context will be string, and we may obtain incorrect output (for example, for "price between 50 and 100").

CodePudding user response：

    WITH vars AS (
        SELECT postid_field,title,
        GROUP_CONCAT(IF(meta_name='color', meta_value, NULL) SEPARATOR '') color,
        GROUP_CONCAT(IF(meta_name='size', meta_value, NULL) SEPARATOR '') size,
        GROUP_CONCAT(IF(meta_name='price', meta_value, NULL) SEPARATOR '') price
        FROM corewp_productmeta,corewp_product
        WHERE postid_field = idname
        GROUP BY group_id,postid_field,title
    )
    select * from vars
    where price > 29 AND price < 59

Query Demo

The query uses the WITH clause to create a sub-query that joins the two tables and assigns the resulting table to a variable eg: vars.

After that, you can query from the variable like any normal table and apply your filters in the where clause and you can filter using the extended columns. eg: where price > 29 AND price < 59

Check the Query Demo on the link above.