Mysql User-defined method and cakephp paging join query method _ MySQL

Mysql User-defined method and cakephp paging join query method bitsCN.com

Mysql User-defined method and cakephp paging join query method

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;

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

BitsCN.com