Mysqlはカスタムメソッドを使用して、cakephpページでjoinで照会する方法を使用します.


もっと読む
STEP 1:SET GLOBALロゴ_を設定します.bin_trust_function_creators=TRUEERROR 1418(HY 000):This function has none of DETERMMINISTIC,NO SQL,or READS SQL DATA in its declaration and binary loging is enabled(you*might*want to use the less safe logloglogs)このようなエラー
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.useruid)AS user_を呼び出します.id,d.dept_id,u.com mpellation FROM zk_アメリカ.departments d INNER JOIN zk_users u ON u.id=d.user_id.  AND  INSTR(u.pininn、'h')=2 WHERE FIND_IN_SET(d.dept_id,get Child Dept(128)GROUT 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');