Mysql使用自定义方法，以及cakephp分页使用join查询

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)这种错误

第二步：

Sql代码  

1. DELIMITER $$   
2.   
3. USE `zhiku`$$   
4.   
5. DROP FUNCTION IF EXISTS `getChildDept`$$   
6.   
7. CREATE  FUNCTION `getChildDept`(rootId INT) RETURNS TEXT CHARSET utf8   
8. BEGIN  
9.     DECLARE sTemp VARCHAR(1000);   
10.     DECLARE sTempChd VARCHAR(1000);   
11.     SET sTemp = '$';   
12.     SET sTempChd =CAST(rootId AS CHAR);   
13.     WHILE sTempChd IS NOT NULL DO   
14.         SET sTemp = CONCAT(sTemp,',',sTempChd);   
15.         SELECT GROUP_CONCAT(id) INTO sTempChd FROM zk_departments WHERE FIND_IN_SET(parent_id,sTempChd)>0;   
16.     END WHILE;   
17.     RETURN sTemp;   
18.     END$$   
19. DELIMITER ;  

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代码  

1. $conditions = array('FIND_IN_SET(dept_id, getChildDept('.$dept_id.'))');   
2.             $condition_join = '`User`.`id` = `UserDepartment`.`user_id`';   
3.             if(!emptyempty($c))$condition_join  .= ' AND INSTR(User.pinyin,"'.$c.'")=2';   
4.             //分页   
5.             $this->paginate = array(   
6.                     'UserDepartment' => array(   
7.                             'conditions' => $conditions,   
8.                             'order'      => array('dept_id'=>'ASC'),   
9.                             'limit'      => 10,   
10.                             'recursive'  => -1,   
11.                             'group'      => array('user_id'),   
12.                             'fields'     => array('user_id','dept_id'),   
13.                             'joins'      => array(array(   
14.                                                  'alias' => 'User',   
15.                                                  'table' => 'zk_users',   
16.                                                  'type' => 'INNER',   
17.                                                  'conditions' => $condition_join,   
18.                                             )),   
19.                     )   
20.             );   
21.             $data = $this->paginate('UserDepartment');  

$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');