mysql使用自定义方法，以及cakephp分页使用join查询的方法
第一步：设置SET GLOBAL log_bin_trust_function_creators=TRUE;
如果报ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)这种错误
第二步：
DELIMITER $$ USE `zhiku`$$ DROP FUNCTION IF EXISTS `getChildDept`$$ CREATE FUNCTION `getChildDept`(rootId INT) RETURNS TEXT CHARSET utf8 BEGIN DECLARE sTemp VARCHAR(1000); DECLARE sTempChd VARCHAR(1000); SET sTemp = '$'; SET sTempChd =CAST(rootId AS CHAR); WHILE sTempChd IS NOT NULL DO SET sTemp = CONCAT(sTemp,',',sTempChd); SELECT GROUP_CONCAT(id) INTO sTempChd FROM zk_departments WHERE FIND_IN_SET(parent_id,sTempChd)>0; END WHILE; RETURN sTemp; END$$ DELIMITER ;
欢迎大家阅读《Mysql应用自定义方法，以及cakephp分页使用join查询的方法》,跪求各位点评，by 搞代码
第三步：直接调用
SELECT DISTINCT(d.user_id) AS user_id,d.dept_id,u.compellation FROM zk_user_departments d INNER JOIN zk_users u ON u.id=d.user_id AND INSTR(u.pinyin,’h’)=2 WHERE FIND_IN_SET(d.dept_id, getChildDept(128)) GROUP BY d.user_id;
放在cakephp为:
$conditions = array('FIND_IN_SET(dept_id, getChildDept('.$dept_id.'))'); $condition_join = '`User`.`id` = `UserDepartment`.`user_id`'; if(!empty($c))$condition_join .= ' AND INSTR(User.pinyin,"'.$c.'")=2'; //分页 $this->paginate = array( 'UserDepartment' => array( 'conditions' => $conditions, 'order' => array('dept_id'=>'ASC'), 'limit' => 10, 'recursive' => -1, 'group' => array('user_id'), 'fields' => array('user_id','dept_id'), 'joins' => array(array( 'alias' => 'User', 'table' => 'zk_users', 'type' => 'INNER', 'conditions' => $condition_join, )), ) ); $data = $this->paginate('UserDepartment');
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：Mysql应用自定义方法，以及cakephp分页使用join查询的方法
转载请注明原文链接：Mysql应用自定义方法，以及cakephp分页使用join查询的方法
