Different price requirement depending on the price currency

Question

I have meta table with the following data:

+----+------------+-----------+------------+
| id | product_id | meta_key  | meta_value |
+----+------------+-----------+------------+
|  1 |          1 | currency  | USD        |
|  2 |          1 | price     | 1100       |
|  3 |          2 | currency  | PLN        |
|  4 |          2 | price     | 1300       |
|  5 |          3 | currency  | USD        |
|  6 |          3 | price     | 1200       |
| 11 |          1 | available | 1          |
| 12 |          2 | available | 1          |
| 13 |          3 | available | 0          |
+----+------------+-----------+------------+

Now I want to fetch product_id if the product is available and the price is above 1000, this can be done with:

SELECT product_id
FROM meta
WHERE meta_key IN ("price", "available") 
GROUP BY product_id 
HAVING 1=1  
    AND SUM(meta_key = "price" AND CAST(meta_value AS DECIMAL(10,2))>=1000) > 0
    AND SUM(meta_key = "available" AND meta_value=1) > 0

Next step is to check the product currency, if I want to fetch products with price above 1000USD, then then product_id=2 shouldn't be returned. The conversion rate for USD/PLN is about 3.63, so 1300PLN is about 357.98USD.

Is there a way to check the product currency and then specify different requirement for the price ? If the currency is USD then the value should be above '1000', if the currency is 'PLN' then the value should be above '3630'.

Answer 1

Thanks for the responses.

I've ended up with the following solution, I have additional currencies table;

+----+------+----------------+
| id | code | conversion_usd |
+----+------+----------------+
|  1 | USD  |       1.000000 |
|  2 | PLN  |       3.650000 |
+----+------+----------------+

and now the query:

SELECT product_id
FROM meta
WHERE product_id IN (
    SELECT price.product_id
    FROM meta AS price
    JOIN meta AS currency ON(price.product_id=currency.product_id AND currency.meta_key='currency')
    JOIN currencies ON(currencies.code=currency.meta_value)
    WHERE
    price.meta_key='price'
    AND price.meta_value/currencies.conversion_usd>1000
)
AND meta_key IN ("available") 
GROUP BY product_id 
HAVING 1=1     
    AND SUM(meta_key = "available" AND meta_value=1) > 0

this way I don't have to use CASE function and it supports as many currencies as I wish.

Answer 2

Giorgos Betsos's solution is correct. However, as a more generic solution, if you write the data out in a non-EAV form, I think you can probably figure out how to answer any further questions you may have along these lines for yourself.

Adapting GB's fiddle (and excluding redundant information from the model)...

drop table if exists meta;

CREATE TABLE meta
    (product_id int, meta_key varchar(12), meta_value varchar(12), PRIMARY KEY(product_id,meta_key))
;

INSERT INTO meta
    (product_id,meta_key,meta_value)
VALUES
    (1, 'currency', 'USD'),
    (1, 'price', '1100'),
    (2, 'currency', 'PLN'),
    (2, 'price', '1300'),
    (3, 'currency', 'USD'),
    (3, 'price', '1200'),
    (1, 'available', '1'),
    (2, 'available', '1'),
    (3, 'available', '0')
;

SELECT product_id 
     , MAX(CASE WHEN meta_key = 'currency' THEN meta_value END) currency
     , MAX(CASE WHEN meta_key = 'price' THEN meta_value END) price
     , MAX(CASE WHEN meta_key = 'available' THEN meta_value END) available
  FROM meta
 GROUP 
    BY product_id;

product_id currency price available
         1 USD       1100         1
         2 PLN       1300         1
         3 USD       1200         0

http://rextester.com/JWW92514

Finally, when using an EAV model I always like to have separate attributes out to different tables according to data type. So you would typically have a table of integer type attributes, a table of decimals, a table of datetimes, and a table of strings.

Answer 3

If you laid out your table differently this request could be possible, and also could be done more efficiently (if your table is very large).

Instead of storing your data with a meta table, use a normal table. Doing things like this creates the inner platform effect, something you want to avoid (you're simulating SQL within a SQL table).

Also, there's the obvious problem of not being able to store prices in multiple currencies for the same product when the supplier wants to apply price discrimination based on country, or wants to make sure the actual price numbers are something customers expect, for example 359 instead of 358, two things that happen to be fairly common.

Create this table and populate it with the product pricing:

+----+------------+----------+-------+
| id | product_id | currency | price |
+----+------------+----------+-------+
|  1 |          1 | USD      |   350 |
|  2 |          2 | USD      |   200 |
|  3 |          3 | PLN      |   800 |
+----+------------+----------+-------+

An example way to do this is as follows:

INSERT INTO `priceTable` (product_id, currency, price) 
SELECT product_id, meta_value, 0 FROM myTable 
WHERE meta_key = 'currency';

UPDATE `priceTable` SET `price` = (SELECT `meta_value` FROM `myTable` 
WHERE `meta_key` = 'price' AND `myTable`.`product_id` = `priceTable`.`product_id`);

Next, for each unique product ID in the table, check if a "USD" row exists. If it does not, then grab the row with the other currency ( I do not know if you have more than 2 currencies in your data), create a new row with the USD currency.

E.g. assuming only one row exists for each product_id with a unique currency, and that currency is either USD or PLN, these 2 queries would do the job (where you use your backend language to transfer the variables using a loop for each selected row.):

SELECT `product_id`, 'USD', ROUND(`price` / 3.63, 0)
FROM `priceTable` WHERE `currency` = 'PLN';

INSERT INTO `priceTable` (`product_id`, `currency`, `price`)
VALUES (:product_id, :currency, :price);

You need to use 2 separate queries as the SELECT and INSERT both reference the same table. Using a second temporary table is also a solution.

By now, your problem has been reduced to a simple:

SELECT `product_id` FROM `priceTable` WHERE `currency` = 'USD' AND price > 1000;

Answer 4

You can use conditional aggregation

SELECT product_id,
       COUNT(CASE 
                WHEN meta_key = 'currency' AND meta_value= 'USD' > 0  THEN 1 
             END) AS USD,
       COUNT(CASE 
                WHEN meta_key = 'currency' AND meta_value= 'PLN' > 0  THEN 1 
             END) AS PLN,
       SUM(CASE 
              WHEN meta_key = 'price' THEN CAST(meta_value AS DECIMAL(10,2)) ELSE 0 
           END) AS price,
       COUNT(CASE WHEN meta_key = 'available' AND meta_value=1 THEN 1 END) AS available
FROM meta
WHERE meta_key IN ('price', 'available', 'currency') 
GROUP BY product_id;

in order to create the following output:

product_id  USD PLN price   available
-------------------------------------
1           1   0   1100,00 1
2           0   1   1300,00 1
3           1   0   1200,00 0

Now you can easily query the above derived table to get the desired result:

SELECT product_id
FROM (
   ... above query here ...
) AS t
WHERE available > 0 AND (USD = 1 AND price > 1000   
                        OR
                        PLN = 1 AND price > 1000*3.63); 

Output:

product_id
----------
1

Demo here