Help me alter this query to get the desired results
- by sandeepan
Please dump these data first
CREATE TABLE IF NOT EXISTS `all_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) unsigned DEFAULT NULL,
PRIMARY KEY (`id_tag_rel`),
KEY `All_Tag_Relations_FKIndex1` (`id_tag`),
KEY `id_wc` (`id_wc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;
INSERT INTO `all_tag_relations` (`id_tag_rel`, `id_tag`, `id_tutor`, `id_wc`) VALUES
(1, 1, 1, NULL),
(2, 2, 1, NULL),
(3, 6, 2, NULL),
(4, 7, 2, NULL),
(8, 3, 1, 1),
(9, 4, 1, 1),
(10, 5, 2, 2),
(11, 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);
CREATE TABLE IF NOT EXISTS `tags` (
`id_tag` int(10) unsigned NOT NULL AUTO_INCREMENT,
`tag` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id_tag`),
UNIQUE KEY `tag` (`tag`),
KEY `id_tag` (`id_tag`),
KEY `tag_2` (`tag`),
KEY `tag_3` (`tag`),
KEY `tag_4` (`tag`),
FULLTEXT KEY `tag_5` (`tag`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
INSERT INTO `tags` (`id_tag`, `tag`) VALUES
(1, 'Sandeepan'),
(2, 'Nath'),
(3, 'first'),
(4, 'class'),
(5, 'new'),
(6, 'Bob'),
(7, 'Cratchit'),
(8, 'more'),
(9, 'fresh'),
(10, 'second');
CREATE TABLE IF NOT EXISTS `webclasses` (
`id_wc` int(10) NOT NULL AUTO_INCREMENT,
`id_author` int(10) NOT NULL,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id_wc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
INSERT INTO `webclasses` (`id_wc`, `id_author`, `name`) VALUES
(1, 1, 'first class'),
(2, 2, 'new class'),
(3, 1, 'more fresh'),
(4, 1, 'second class'),
(5, 2, 'sandeepan class');
About the system
- The system consists of tutors and classes.
- The data in the table All_Tag_Relations stores tag relations for each tutor registered and each class created by a tutor. The tag relations are used for searching classes.
The current data dump corresponds to tutor "Sandeepan Nath" who has created classes named "first class", "more fresh", "second class" and tutor "Bob Cratchit" who has created classes "new class" and "Sandeepan class".
I am trying for a search query performs AND logic on the search keywords and returns wvery such class for which the search terms are present in the class name or its tutor name
To make it easy, following is the list of search terms and desired results:-
Search term result classes (check the id_wc in the results)
first class 1
Sandeepan Nath class 1
Sandeepan Nath 1,3
Bob Cratchit 2
Sandeepan Nath bob none
Sandeepan Class 1,4,5
I have so far reached upto this query
-- Two keywords search
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
) ) AS key_1_class_matches,
sum( DISTINCT (
wtagrels.id_tag = @tag2
) ) AS key_2_class_matches,
sum( DISTINCT (
ttagrels.id_tag = @tag1
) ) AS key_1_tutor_matches,
sum( DISTINCT (
ttagrels.id_tag = @tag2
) ) AS key_2_tutor_matches,
sum( DISTINCT (
ttagrels.id_tag = wtagrels.id_tag
) ) AS key_class_tutor_matches
FROM WebClasses as wc
join all_tag_relations AS wtagrels on wc.id_wc = wtagrels.id_wc
join all_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
LIMIT 0 , 20
For search with 1 or 3 terms, remove/add the variable part in this query. Tabulating my observation of the values of key_1_class_matches, key_2_class_matches,key_1_tutor_matches (say, class keys),key_2_tutor_matches for various cases (say, tutor keys).
Search term expected result Observation
first class 1 for class 1, all class keys+all tutor keys =1
Sandeepan Nath class 1 for class 1, one class key+ all tutor keys = 1
Sandeepan Nath 1,3 both tutor keys =1 for these classes
Bob Cratchit 2 both tutor keys = 1
Sandeepan Nath bob none no complete tutor matches for any class
I found a pattern that, for any case, the class(es) which should appear in the result have the highest number of matches (all class keys and tutor keys).
E.g. searching "first class", only for class =1, total of key matches = 4(1+1+1+1)
searching "Sandeepan Nath", for classes 1, 3,4(all classes by Sandeepan Nath) have all the tutor keys matching.
But no pattern in the search for "Sandeepan Class" - classes 1,4,5 should match.
Now, how do I put a condition into the query, based on that pattern so that only those classes are returned.
Do I need to use full text search here because it gives a scoring/rank value indicating the strength of the match? Any sample query would help.
Please note - I have already found solution for showing classes when any/all of the search terms match with the class name. http://stackoverflow.com/questions/3030022/mysql-help-me-alter-this-search-query-to-get-desired-results But if all the search terms are in tutor name, it does not work. So, I am modifying the query and experimenting.
