Last active
May 4, 2024 11:54
-
-
Save abdelaziz321/5f9e629411d6d05697358dac43018999 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- === DDL | |
CREATE TABLE categories ( | |
id INT PRIMARY KEY AUTO_INCREMENT, | |
name CHAR(50) NOT NULL | |
); | |
CREATE TABLE items ( | |
id INT PRIMARY KEY AUTO_INCREMENT, | |
name CHAR(50) NOT NULL, | |
created_at TIMESTAMP, | |
category_id INT NOT NULL, | |
FOREIGN KEY (category_id) REFERENCES categories(id) | |
); | |
CREATE TABLE categories_properties ( | |
id int NOT NULL PRIMARY KEY, | |
name CHAR(100) NOT NULL , | |
category_id INT NOT NULL, | |
FOREIGN KEY (category_id) REFERENCES categories(id) | |
); | |
CREATE TABLE items_properties ( | |
value CHAR(100) NOT NULL, | |
category_property_id INT NOT NULL, | |
item_id INT NOT NULL, | |
category_id INT NOT NULL, | |
PRIMARY KEY (category_property_id, item_id, category_id), | |
FOREIGN KEY (category_property_id) REFERENCES categories_properties(id), | |
FOREIGN KEY (item_id) REFERENCES items(id), | |
FOREIGN KEY (category_id) REFERENCES categories(id) | |
); | |
-- === seeding | |
-- categories | |
INSERT INTO categories SET id=1, name='clothes'; | |
INSERT INTO categories SET id=2, name='mobiles'; | |
-- items | |
INSERT INTO items SET id=1, name='shirt', category_id=1; | |
INSERT INTO items SET id=2, name='pents', category_id=1; | |
INSERT INTO items SET id=3, name='short', category_id=1; | |
INSERT INTO items SET id=4, name='mob 1', category_id=2; | |
INSERT INTO items SET id=5, name='mob 2', category_id=2; | |
INSERT INTO items SET id=6, name='mob 3', category_id=2; | |
-- categories_properties | |
INSERT INTO categories_properties SET id=1, name='size', category_id=1; | |
INSERT INTO categories_properties SET id=2, name='color', category_id=1; | |
INSERT INTO categories_properties SET id=3, name='Brand', category_id=2; | |
INSERT INTO categories_properties SET id=4, name='price', category_id=2; | |
-- items_properties | |
INSERT INTO items_properties SET category_id=1, item_id=1, category_property_id=1, value='md'; | |
INSERT INTO items_properties SET category_id=1, item_id=1, category_property_id=2, value='white'; | |
INSERT INTO items_properties SET category_id=1, item_id=2, category_property_id=1, value='42'; | |
INSERT INTO items_properties SET category_id=1, item_id=2, category_property_id=2, value='black'; | |
INSERT INTO items_properties SET category_id=1, item_id=3, category_property_id=1, value='XL'; | |
INSERT INTO items_properties SET category_id=1, item_id=3, category_property_id=2, value='black'; | |
INSERT INTO items_properties SET category_id=2, item_id=4, category_property_id=3, value='samesung'; | |
INSERT INTO items_properties SET category_id=2, item_id=4, category_property_id=4, value='2000'; | |
INSERT INTO items_properties SET category_id=2, item_id=5, category_property_id=3, value='nokia'; | |
INSERT INTO items_properties SET category_id=2, item_id=5, category_property_id=4, value='2100'; | |
INSERT INTO items_properties SET category_id=2, item_id=6, category_property_id=3, value='iphone'; | |
INSERT INTO items_properties SET category_id=2, item_id=6, category_property_id=4, value='20000'; | |
-- === DML | |
-- select * items | |
SELECT items.id, items.name AS item, items.created_at, categories.name AS category, JSON_OBJECTAGG(categories_properties.name, items_properties.value) AS properties | |
FROM categories | |
INNER JOIN items | |
ON categories.id = items.category_id | |
INNER JOIN categories_properties | |
ON categories.id = categories_properties.category_id | |
INNER JOIN items_properties | |
ON categories.id = items_properties.category_id | |
AND | |
items.id = items_properties.item_id | |
AND | |
categories_properties.id = items_properties.category_property_id | |
GROUP BY items.id | |
-- select * items from `clothes` category with black color | |
SELECT items.id, items.name AS item, categories.name AS category, items.created_at | |
FROM items | |
INNER JOIN categories | |
ON categories.id = items.category_id | |
INNER JOIN categories_properties | |
ON categories.id = categories_properties.category_id | |
INNER JOIN items_properties | |
ON categories.id = items_properties.category_id | |
AND | |
items.id = items_properties.item_id | |
AND | |
categories_properties.id = items_properties.category_property_id | |
WHERE | |
categories.name = 'clothes' | |
AND | |
categories_properties.name = 'color' | |
AND | |
items_properties.value = 'black' |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment