database.sql

-- === 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'