1

I have 3 tables: CV, CvSkill, Skill. CvSkill is many-to-many map table between other two tables. So what I want to achieve is, when user searches for skill, using Sphinx to get all CVs related to searched skill. Also user can mistype something in skills. Sphinx must match entered skill by proximity and get maximally relevant skills' CV results.

I can't figure out how to configure sphinx (etc/sphinxsearch/sphinx.conf) to search this way.

Any suggestions?

CREATE TABLE `CV` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `fullName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Ad, soyad'
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT;


CREATE TABLE `CvSkill` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cvID` int(10) unsigned DEFAULT NULL,
  `skillID` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `CS_UK1` (`cvID`,`skillID`),
  KEY `skill_id` (`skillID`),
  CONSTRAINT `CS_FK1` FOREIGN KEY (`cvID`) REFERENCES `CV` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `CS_FK2` FOREIGN KEY (`skillID`) REFERENCES `Skill` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=utf8;

CREATE TABLE `Skill` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) DEFAULT NULL,
  `logoImg` varchar(255) DEFAULT NULL,
  `color` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;

Thanks in advance

heron
  • 3,611
  • 25
  • 80
  • 148

1 Answers1

1

Could just join the tables.. 

sql_query = SELECT CV.id,GROUP_CONCAT(Skill.name SEPARATOR '. ') as skill \
  FROM CV \
  INNER JOIN CvSkill ON (cvID = CV.id) \
  INNER JOIN Skill ON (skillID = Skill.id) \
  GROUP BY CV.id \
  ORDER BY NULL

of course may want additional fields to be able to search, but the above is a start.

barryhunter
  • 20,886
  • 3
  • 30
  • 43
  • Is CvSkill table used in query at all? I don't see – heron Nov 14 '15 at 02:32
  • Yes, you need it in the join condition . As it contains the link between CV and the Skill table – barryhunter Nov 14 '15 at 13:08
  • be careful with GROUP_CONCAT() there is a LENGTH limit (not a count limit ) * https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_group_concat_max_len * http://stackoverflow.com/questions/23608464/group-concat-with-limit * http://stackoverflow.com/questions/2567000/mysql-and-group-concat-maximum-length – Moosh Mar 25 '17 at 08:10