欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-04-17 23:34:10
...
bitsCN.com

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

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

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

//分页

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

bitsCN.com
相关标签: cakephp