Code:
CREATE TABLE `lessons` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`user_id` mediumint(8) unsigned NOT NULL,
`title` varchar(150) collate utf8_unicode_ci NOT NULL,
`description` mediumtext collate utf8_unicode_ci NOT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `lesson_attributes` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`lesson_id` mediumint(8) unsigned NOT NULL,
`attribute_type` char(11) collate utf8_unicode_ci NOT NULL,
`attribute_id` smallint(4) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `attribute_id` (`lesson_id`),
KEY `attribute_type` (`attribute_type`,`attribute_id`)
) ENGINE=InnoDB;
CREATE TABLE `attributes` (
`id` smallint(4) unsigned NOT NULL auto_increment,
`type` char(11) collate utf8_unicode_ci NOT NULL,
`value` char(30) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `type` (`type`)
) ENGINE=InnoDB;
INSERT INTO `attributes` VALUES ('2', 'style', 'Vintage Rock');
INSERT INTO `attributes` VALUES ('3', 'style', 'Hard Rock');
INSERT INTO `attributes` VALUES ('4', 'style', 'Modern Rock');
INSERT INTO `attributes` VALUES ('5', 'style', 'Heavy Metal');
INSERT INTO `attributes` VALUES ('6', 'style', 'Progressive Metal');
INSERT INTO `attributes` VALUES ('7', 'style', 'Extreme Metal');
INSERT INTO `attributes` VALUES ('18', 'style', 'Punk');
INSERT INTO `attributes` VALUES ('19', 'technique', 'Alternate Picking');
INSERT INTO `attributes` VALUES ('20', 'technique', 'Sweep Picking');
INSERT INTO `attributes` VALUES ('21', 'technique', 'Legato (hammer on / pull off)');
INSERT INTO `attributes` VALUES ('30', 'technique', 'Slide');
INSERT INTO `attributes` VALUES ('31', 'type', 'Ballad');
INSERT INTO `attributes` VALUES ('32', 'type', 'Etude');
INSERT INTO `attributes` VALUES ('33', 'type', 'Song');
INSERT INTO `attributes` VALUES ('36', 'type', 'Riffing');
INSERT INTO `attributes` VALUES ('37', 'type', 'Theory');
INSERT INTO `attributes` VALUES ('38', 'difficulty', 'Beginner');
INSERT INTO `attributes` VALUES ('39', 'difficulty', 'Beginner/Intermediate');
INSERT INTO `attributes` VALUES ('40', 'difficulty', 'Intermediate');
INSERT INTO `attributes` VALUES ('41', 'difficulty', 'Intermediate/Advanced');
INSERT INTO `attributes` VALUES ('42', 'difficulty', 'Advanced');
INSERT INTO `lesson_attributes` VALUES ('1', '666', 'difficulty', '40');
INSERT INTO `lesson_attributes` VALUES ('2', '666', 'style', '3');
INSERT INTO `lesson_attributes` VALUES ('3', '666', 'style', '5');
INSERT INTO `lesson_attributes` VALUES ('4', '666', 'style', '6');
INSERT INTO `lesson_attributes` VALUES ('5', '666', 'technique', '27');
INSERT INTO `lesson_attributes` VALUES ('6', '666', 'type', '35');
INSERT INTO `lesson_attributes` VALUES ('7', '777', 'style', '17');
INSERT INTO `lesson_attributes` VALUES ('8', '777', 'difficulty', '40');
INSERT INTO `lesson_attributes` VALUES ('9', '555', 'difficulty', '40');
INSERT INTO `lesson_attributes` VALUES ('10', '555', 'style', '3');
INSERT INTO `lesson_attributes` VALUES ('11', '444', 'style', '3');
INSERT INTO `lesson_attributes` VALUES ('12', '444', 'difficulty', '42');
CREATE TABLE `lessons` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`user_id` mediumint(8) unsigned NOT NULL,
`title` varchar(150) collate utf8_unicode_ci NOT NULL,
`description` mediumtext collate utf8_unicode_ci NOT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `lesson_attributes` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`lesson_id` mediumint(8) unsigned NOT NULL,
`attribute_type` char(11) collate utf8_unicode_ci NOT NULL,
`attribute_id` smallint(4) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `attribute_id` (`lesson_id`),
KEY `attribute_type` (`attribute_type`,`attribute_id`)
) ENGINE=InnoDB;
CREATE TABLE `attributes` (
`id` smallint(4) unsigned NOT NULL auto_increment,
`type` char(11) collate utf8_unicode_ci NOT NULL,
`value` char(30) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `type` (`type`)
) ENGINE=InnoDB;
INSERT INTO `attributes` VALUES ('2', 'style', 'Vintage Rock');
INSERT INTO `attributes` VALUES ('3', 'style', 'Hard Rock');
INSERT INTO `attributes` VALUES ('4', 'style', 'Modern Rock');
INSERT INTO `attributes` VALUES ('5', 'style', 'Heavy Metal');
INSERT INTO `attributes` VALUES ('6', 'style', 'Progressive Metal');
INSERT INTO `attributes` VALUES ('7', 'style', 'Extreme Metal');
INSERT INTO `attributes` VALUES ('18', 'style', 'Punk');
INSERT INTO `attributes` VALUES ('19', 'technique', 'Alternate Picking');
INSERT INTO `attributes` VALUES ('20', 'technique', 'Sweep Picking');
INSERT INTO `attributes` VALUES ('21', 'technique', 'Legato (hammer on / pull off)');
INSERT INTO `attributes` VALUES ('30', 'technique', 'Slide');
INSERT INTO `attributes` VALUES ('31', 'type', 'Ballad');
INSERT INTO `attributes` VALUES ('32', 'type', 'Etude');
INSERT INTO `attributes` VALUES ('33', 'type', 'Song');
INSERT INTO `attributes` VALUES ('36', 'type', 'Riffing');
INSERT INTO `attributes` VALUES ('37', 'type', 'Theory');
INSERT INTO `attributes` VALUES ('38', 'difficulty', 'Beginner');
INSERT INTO `attributes` VALUES ('39', 'difficulty', 'Beginner/Intermediate');
INSERT INTO `attributes` VALUES ('40', 'difficulty', 'Intermediate');
INSERT INTO `attributes` VALUES ('41', 'difficulty', 'Intermediate/Advanced');
INSERT INTO `attributes` VALUES ('42', 'difficulty', 'Advanced');
INSERT INTO `lesson_attributes` VALUES ('1', '666', 'difficulty', '40');
INSERT INTO `lesson_attributes` VALUES ('2', '666', 'style', '3');
INSERT INTO `lesson_attributes` VALUES ('3', '666', 'style', '5');
INSERT INTO `lesson_attributes` VALUES ('4', '666', 'style', '6');
INSERT INTO `lesson_attributes` VALUES ('5', '666', 'technique', '27');
INSERT INTO `lesson_attributes` VALUES ('6', '666', 'type', '35');
INSERT INTO `lesson_attributes` VALUES ('7', '777', 'style', '17');
INSERT INTO `lesson_attributes` VALUES ('8', '777', 'difficulty', '40');
INSERT INTO `lesson_attributes` VALUES ('9', '555', 'difficulty', '40');
INSERT INTO `lesson_attributes` VALUES ('10', '555', 'style', '3');
INSERT INTO `lesson_attributes` VALUES ('11', '444', 'style', '3');
INSERT INTO `lesson_attributes` VALUES ('12', '444', 'difficulty', '42');
SQL 1
Code:
SELECT DISTINCT lesson_attributes.lesson_id
FROM lesson_attributes
WHERE
(lesson_attributes.attribute_type = 'style' AND lesson_attributes.attribute_id = '3')
OR
(lesson_attributes.attribute_type = 'difficulty' AND lesson_attributes.attribute_id = '40')
SELECT DISTINCT lesson_attributes.lesson_id
FROM lesson_attributes
WHERE
(lesson_attributes.attribute_type = 'style' AND lesson_attributes.attribute_id = '3')
OR
(lesson_attributes.attribute_type = 'difficulty' AND lesson_attributes.attribute_id = '40')
SQL 2
Code:
SELECT DISTINCT a.lesson_id
FROM lesson_attributes AS a
Inner Join lesson_attributes AS a1 ON a1.lesson_id = a.lesson_id AND a1.attribute_type = 'style' AND a1.attribute_id = 3
Inner Join lesson_attributes AS a2 ON a2.lesson_id = a.lesson_id AND a2.attribute_type = 'difficulty' AND a2.attribute_id = 40
SELECT DISTINCT a.lesson_id
FROM lesson_attributes AS a
Inner Join lesson_attributes AS a1 ON a1.lesson_id = a.lesson_id AND a1.attribute_type = 'style' AND a1.attribute_id = 3
Inner Join lesson_attributes AS a2 ON a2.lesson_id = a.lesson_id AND a2.attribute_type = 'difficulty' AND a2.attribute_id = 40
Ovo je dio strukture baze tj. najvazniji dio za ovaj problem.
Svaki lesson moze sadrzavat vise razlicitih atributa (style, difficulty, type ili technique) i to mi je zakomplikovalo stvar tj. do komplikacija dodje kad hocu da filtriram lessone.
Npr. trebju mi svi lessoni koji imaju sljedece atribute difficulty=40 i style=3, skontao sam rjesenjem sa vise JOIN-a (SQL 2) ali mi se ne svidja, komplikovano je i nema bas dobre performanse. Rjesenje SQL 1 koje je "elegantnije" pokupi lessone koji imaju difficulty=40 ili style=3 a to mi ne treba.
Dakle SQL 2 radi ali vjerujem da postoji jednostavnije rjesenje ali ga ne vidim :)
Bogdane, misljenje? :D
NO FATE. ONLY THE POWER OF WILL.