Different price requirements depending on the currency of the price
I have a 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 get the 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
The next step is to check the currency of the product, if I want to get products with a price above $ 1000, then product_id = 2 should not be returned. The conversion rate for USD / PLN is around 3.63, so 1300PLN is around $ 357.98.
Is there a way to check the currency of the product and then specify different price requirements? If the currency is US dollar, then the value should be above "1000", if the currency is "PLN" then the value should be above "3630".
source to share
Thanks for answers.
I got the following solution: I have a table of additional currencies;
+----+------+----------------+
| id | code | conversion_usd |
+----+------+----------------+
| 1 | USD | 1.000000 |
| 2 | PLN | 3.650000 |
+----+------+----------------+
and now the request:
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 need to use the CASE function and it supports as many currencies as I want.
source to share
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;
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 resulting table above to get the desired output:
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
source to share
If you lay out your table in different ways, this query may be possible, and it can also be done more efficiently (if your table is very large).
Instead of storing your data with a meta table, use a regular table. Doing things like this creates an intrinsic platform effect that you want to avoid (you are modeling SQL in an SQL table).
Also, there is the obvious problem of not being able to store prices in different currencies for the same product when a supplier wants to discriminate against a country or wants to make sure the actual prices are what customers expect, for example 359 instead of 358, two things. which happen quite often.
Create this table and fill it in with the product price:
+----+------------+----------+-------+
| id | product_id | currency | price |
+----+------------+----------+-------+
| 1 | 1 | USD | 350 |
| 2 | 2 | USD | 200 |
| 3 | 3 | PLN | 800 |
+----+------------+----------+-------+
An example of this method looks like this:
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`);
Then, for each unique product ID in the table, check if the "USD" row exists. If it is not, then take a line with a different currency (I don't know if you have more than two currencies in your data), create a new line with USD.
eg. assuming product_id
there is only one row for each single currency, and that currency is USD or PLN, these 2 queries will do the job (where you use your internal language to wrap 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 request, as SELECT
and INSERT
both refer to the same table. Using a second temporary table is also a solution.
By now, your problem boils down to a simple one:
SELECT `product_id` FROM `priceTable` WHERE `currency` = 'USD' AND price > 1000;
source to share
Giorgos Betsos' decision is correct. However, as a more general solution, if you are writing data in a non-EAV form, I think you can probably figure out how to answer any other questions you might have along these lines for yourself.
Adapting the GB script (and removing 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
Finally, when using the EAV model, I always like separate attributes for different tables according to the data type. Thus, you should usually have an integer type attribute table, decimal point table, time table, and string table.
source to share