Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- === 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