Filtriranje podataka

[ ihti @ 01.06.2010. 02:09 ] @

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');

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

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

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