Mysql uses custom methods and cakephp uses join queries on pages
Step 1: set global log_bin_trust_function_creators = TRUE;
If ERROR 1418 (HY000) is reported: 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)
Step 2:
SQL code
- 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_levels WHERE FIND_IN_SET (parent_id, sTempChd)> 0;
- End while;
- RETURN sTemp;
- END $
- DELIMITER;
DELIMITER $$USE `zhiku`$$DROP FUNCTION IF EXISTS `getChildDept`$$CREATE FUNCTION `getChildDept`(rootId INT) RETURNS TEXT CHARSET utf8BEGINDECLARE sTemp VARCHAR(1000);DECLARE sTempChd VARCHAR(1000);SET sTemp = '$';SET sTempChd =CAST(rootId AS CHAR);WHILE sTempChd IS NOT NULL DOSET 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 ;
Step 3: directly call
Select distinct (d. user_id) AS user_id, d. dept_id, u. compellation FROM zk_user_ments ments 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, Fig (128) group by d. user_id;
Put it in cakephp as follows:
Php code
- $ 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 ';
- // Pagination
- $ This-> paginate = array (
- 'Userdepartment '=> array (
- 'Conditions' => $ conditions,
- 'Order' => array ('Dept _ id' => 'asc '),
- 'Limit' => 10,
- 'Authorization' =>-1,
- 'Group' => array ('User _ id '),
- 'Fields' => array ('User _ id', 'Dept _ id '),
- 'Joins' => array (
- 'Alias' => 'user ',
- 'Table' => 'zk _ users ',
- 'Type' => 'inner ',
- 'Condition' => $ condition_join,
- )),
- )
- );
- $ 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'; // page $ this-> paginate = array ('userdepartment' => array ('conditions' => $ conditions, 'order' => array ('Dept _ id' => 'asc'), 'limit' => 10, 'recursion' =>-1, 'Group' => array ('User _ id'), 'fields' => array ('User _ id', 'Dept _ id '), 'join' => array ('Alias' => 'user', 'table' => 'zk _ users', 'type' => 'inner ', 'condition' => $ condition_join,),); $ data = $ this-> paginate ('userdepartment ');