query which performs AND logic on the keywords and returns every such class for which the search terms are present in the class or its tutor name

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开发者_StackOverflow` 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 to get a search query which performs AND logic on the search keywords and returns every such class for which the search terms are present in the class name or its tutor name.

To make it easy, following is an example 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. 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.

---

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.

---

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