Mysql uses custom methods and cakephp uses join queries on pages

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

1. DELIMITER $
3. USE 'zhiku '$
5. Drop function if exists 'getchilddept '$
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_levels 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 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

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. // Pagination
5. $ This-> paginate = array (
6. 'Userdepartment '=> array (
7. 'Conditions' => $ conditions,
8. 'Order' => array ('Dept _ id' => 'asc '),
9. 'Limit' => 10,
10. 'Authorization' =>-1,
11. 'Group' => array ('User _ id '),
12. 'Fields' => array ('User _ id', 'Dept _ id '),
13. 'Joins' => array (
14. 'Alias' => 'user ',
15. 'Table' => 'zk _ users ',
16. 'Type' => 'inner ',
17. 'Condition' => $ 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'; // 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 ');