mysql - 使用 cakephp 和 mysql 进行连接的 OR 条件

Question

我在 solr 中搜索关键字，它返回匹配的简历 ID，当我从 solr 获得结果时，我正在搜索这些文档并从我的数据库中获取匹配的求职者。现在我的问题是必须获取记录是否与文档或求职者技能匹配，即 job_seekers.skills 列如何编写查询..

这是我现有的查询..

SELECT * 
FROM  `job_seekers` AS `JobSeeker` 
LEFT JOIN `job_seeker_documents` AS `JobSeekerDocument` ON (`JobSeeker`.`id` = `JobSeekerDocument`.`job_seeker_id` AND `doc_attachment` IN ('1457448773Jan.doc',  '1457448764Eric.doc', 'Vijal_Chokshi_Profile.doc', 'Deborah_Project manager_Profile..docx'))
LEFT JOIN `config_work_authorizations` AS `ConfigWorkAuthorization` ON (`JobSeeker`.`work_authorization` = `ConfigWorkAuthorization`.`id`)
LEFT JOIN `employees` AS `Employee` ON (`Employee`.`job_seeker_id` = `JobSeeker`.`id`)
WHERE `JobSeeker`.`company_id` = 11 AND `JobSeeker`.`skills` = 'java'  AND `JobSeeker`.`bench_status` IN (0, 1) AND (select count(*) from employee_jobs where `employee_jobs`.`employee_id`=`Employee`.`id` and `employee_jobs`.`job_status` =1 ) = 0  GROUP BY `JobSeeker`.`id` HAVING (COUNT(`JobSeekerDocument`.`id`) > 0 );

Answer 1

您可以在括号中写入或条件，即()，例如

(`JobSeeker`.`skills` = 'java'  OR `JobSeekerDocument`.`skills` = 'java')

在您的查询中

SELECT * 
FROM  `job_seekers` AS `JobSeeker` 
LEFT JOIN `job_seeker_documents` AS `JobSeekerDocument` ON (`JobSeeker`.`id` = `JobSeekerDocument`.`job_seeker_id` AND `doc_attachment` IN ('1457448773Jan.doc',  '1457448764Eric.doc', 'Vijal_Chokshi_Profile.doc', 'Deborah_Project manager_Profile..docx'))
LEFT JOIN `config_work_authorizations` AS `ConfigWorkAuthorization` ON (`JobSeeker`.`work_authorization` = `ConfigWorkAuthorization`.`id`)
LEFT JOIN `employees` AS `Employee` ON (`Employee`.`job_seeker_id` = `JobSeeker`.`id`)
WHERE `JobSeeker`.`company_id` = 11 
AND (`JobSeeker`.`skills` = 'java'  OR `JobSeekerDocument`.`skills` = 'java')
AND `JobSeeker`.`bench_status` IN (0, 1) 
AND (select count(*) from employee_jobs where `employee_jobs`.`employee_id`=`Employee`.`id` and `employee_jobs`.`job_status` =1 ) = 0  
GROUP BY `JobSeeker`.`id` 
HAVING (COUNT(`JobSeekerDocument`.`id`) > 0 );