ansaurus

Question

Help me alter this query to get the desired results - Resolved

Answer 1

+1 A:

I think that your design is not so good, and hard to understand because you are mixing tutors and tags in the same table. That's why it is even harder to do a query to get the results you want. In your examples with Search term and result classes it is hard to tell when you want to search by tag(=word in class name) or by tag(=name of tutor)

I suggest to use the following tables:
(If in tags you only have the words in the name of webclasses, you could search in that table with LIKE, and drop tables tags and all_tag_relations.)

CREATE TABLE `tutors` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `tutors` (`id`,`name`) VALUES (1,'Sandeepan Nath'), (2,'Bob Cratchit');

CREATE TABLE `webclasses` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_tutor` int(10) unsigned NOT NULL,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `webclasses` (`id`, `id_tutor`, `name`) VALUES
(1, 1, 'first class'),(2, 2, 'new class'),(3, 1, 'more fresh'), (4, 1, 'second class'),(5, 2, 'sandeepan class');

CREATE TABLE `tags` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tag` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `tags` (`id`, `tag`) VALUES
(1, 'sandeepan'),(3, 'first'),(4, 'class'),(5, 'new'),(8, 'more'),(9, 'fresh'),(10, 'second');

CREATE TABLE `all_tag_relations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_tag` int(10) unsigned NOT NULL,
  `id_wc` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Tags of classes';
INSERT INTO `all_tag_relations` (`id_tag`, `id_wc`) VALUES
(3, 1),(4, 1),(5, 2),(4, 2),(8, 3),(9, 3),(10,4),(4, 4),(1, 5),(4, 5);

Searching by tags:

SELECT wc.*, COUNT(*) as tag_count, rel.id_tag from webclasses wc
LEFT JOIN all_tag_relations rel ON wc.id = rel.id_wc
WHERE (rel.id_tag = 3 OR rel.id_tag=4)
GROUP BY wc.id
HAVING tag_count!=1;

I put tag_count!=1 because, if you add conditions for tutors, tag_count will be 0 when all the tags are not in class name. Otherwise, tag_count is always greater than 0.

I would suggest using REGEXP for partial matching, for example, if the user enters firs clas, the query would be

SELECT * FROM webclasses 
WHERE 1 
      AND name REGEXP '([[:<:]]firs.*)'
      AND name REGEXP '([[:<:]]clas.*)'

(I put WHERE 1 because in your application you can put the conditions in a foreach loop with AND name..., after you split the text by whitespaces.)

The result is class 1 - first class

To select data from the tutors table too, you can create a view from the following query:

SELECT wc.*, tut.name AS `tutor_name` FROM webclasses wc
LEFT JOIN tutors tut ON wc.id_tutor = tut.id;

and add some extra conditions in the previous query.

P.S. Why you say that for Bob Cratchit the result classes should be only 2 and not 2, 5? because 5 is his class too.

True Soft 2010-06-16 21:48:39

You are correct that the design was not so good. The tag relations need to be kept separate.

sandeepan 2010-06-20 18:27:33

Answer 2

A:

I have found the solution myself. I feel so happy!!!

The key lies in separate tag relations tables for tutors and classes. i.e. two tables called tutors_tag_relations and webclasses_tag_relations instead of the all_tag_relations as follows:-

CREATE TABLE IF NOT EXISTS `tutors_tag_relations` (
  `id_tag_rel` int(10) NOT NULL AUTO_INCREMENT,
  `id_tag` int(10) unsigned NOT NULL DEFAULT '0',
  `id_tutor` int(10) DEFAULT NULL,
  PRIMARY KEY (`id_tag_rel`),
  KEY `All_Tag_Relations_FKIndex1` (`id_tag`),
  KEY `id_tag` (`id_tag`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;


INSERT INTO `tutors_tag_relations` (`id_tag_rel`, `id_tag`, `id_tutor`) VALUES
(1, 1, 1),
(2, 2, 1),
(3, 6, 2),
(4, 7, 2);


CREATE TABLE IF NOT EXISTS `webclasses_tag_relations` (
  `id_tag_rel` int(10) NOT NULL AUTO_INCREMENT,
  `id_tag` int(10) unsigned NOT NULL DEFAULT '0',
  `id_tutor` int(10) DEFAULT NULL,
  `id_wc` int(10) DEFAULT NULL,
  PRIMARY KEY (`id_tag_rel`),
  KEY `webclasses_Tag_Relations_FKIndex1` (`id_tag`),
  KEY `id_wc` (`id_wc`),
  KEY `id_tag` (`id_tag`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;

INSERT INTO `webclasses_tag_relations` (`id_tag_rel`, `id_tag`, `id_tutor`, `id_wc`) VALUES
(1, 3, 1, 1),
(2, 4, 1, 1),
(3, 5, 2, 2),
(4, 4, 2, 2),
(15, 8, 1, 3),
(16, 9, 1, 3),
(17, 10, 1, 4),
(18, 4, 1, 4),
(19, 1, 2, 5),
(20, 4, 2, 5);

Then this query works:-

SET @tag1 = 4, @tag2 = 1; -- Setting some user variables to see where the ids go.

SELECT wc.id_wc, sum( DISTINCT (
wtagrels.id_tag = @tag1 or ttagrels.id_tag = @tag1
) ) AS key_1_matches,
sum( DISTINCT (
wtagrels.id_tag = @tag2 or ttagrels.id_tag = @tag2
) ) AS key_2_matches

FROM WebClasses as wc
join webclasses_tag_relations AS wtagrels on wc.id_wc = wtagrels.id_wc
join tutors_tag_relations as ttagrels on (wc.id_author = ttagrels.id_tutor)

WHERE (
wtagrels.id_tag = @tag1
OR wtagrels.id_tag = @tag2
OR ttagrels.id_tag = @tag1
OR ttagrels.id_tag = @tag2
)
GROUP BY wtagrels.id_wc
having key_1_matches = 1 and key_2_matches=1
LIMIT 0 , 20

sandeepan 2010-06-20 18:31:15

Congratulations! The most important thing is to make things simple, even though that means an extra table in your db.

True Soft 2010-06-20 20:44:13

ansaurus

tags:

views:

answers:

Help me alter this query to get the desired results - Resolved

related questions