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)這種錯誤 www.2cto.com 第二步： Sql代碼 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 ; 第三步：直接調用 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為: Php代碼 $conditions = array('FIND_IN_SET(dept_id, getChildDept('.$dept_id.'))'); $condition_join = '`User`.`id` = `UserDepartment`.`user_id`'; if(!emptyempty($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');
