在叁个种类中,要求查询表tr_product中的user_id字段,如果user_id同样,则把其相应的money字段相加,数据库截图如下:

Mysql使用自定义方法以及cakephp分页使用join查询的不二秘技

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

Query Builder

[php] view
plain
copy

 

  1. $rows = (new \yii\db\Query())  
  2.     ->select([‘dyn_id’, ‘dyn_name’])  
  3.     ->from(‘zs_dynasty’)  
  4.     ->where([‘between’,’dyn_id’, 1,30])  
  5.     ->limit(10)  
  6.     ->all();  
  7. print_r($rows);  

 

[php] view
plain
copy

 

金沙注册送58 1金沙注册送58 2

  1. use yii\db\Query;  
  2. $query = (new Query())  
  3.     ->from(‘user’)  
  4.     ->orderBy(‘id’);  

 

SELECT

 

[php] view
plain
copy

 

金沙注册送58 3金沙注册送58 4

  1. $query->select(‘*’)->  
  2. select(‘dyn_id as id, dynasty.dyn_name’)->  
  3. $query->select([‘dyn_id as id’, “CONCAT(dyn_name,’a’)”])->  
  4. $query->select(‘user_id’)->distinct()->  

 

 

 

FORM

 

[php] view
plain
copy

 

金沙注册送58 5金沙注册送58 6

  1. $query->from(‘user’);  
  2. $query->from([‘public.user u’, ‘public.post p’]);  
  3. $query->from(‘public.user u, public.post p’);  
  4. $query->from([‘u’ => ‘public.user’, ‘p’ => ‘public.post’]);  
  5. ———-  
  6. $subQuery = (new Query())->select(‘id’)->from(‘user’)->where(‘status=1’);  
  7. // SELECT * FROM (SELECT `id` FROM `user` WHERE status=1) u   
  8. $query->from([‘u’ => $subQuery]);  

 

WHERE

 

[php] view
plain
copy

 

金沙注册送58 7金沙注册送58 8

  1. where(‘status=1’)->  
  2. where(‘status=:status’, [‘:status’ => $status])->  
  3. where([  
  4.     ‘status’ => 10,  
  5.     ‘type’ => null,  
  6.     ‘id’ => [4, 8, 15],  
  7. ])->  
  8. ——-  
  9. $userQuery = (new Query())->select(‘id’)->from(‘user’);  
  10. // …WHERE `id` IN (SELECT `id` FROM `user`)  
  11. $query->…->where([‘id’ => $userQuery])->…  
  12. ——–  
  13. [‘and’, ‘id=1’, ‘id=2’] //id=1 AND id=2  
  14. [‘and’, ‘type=1’, [‘or’, ‘id=1’, ‘id=2’]] //type=1 AND (id=1 OR id=2)  
  15. [‘between’, ‘id’, 1, 10] //id BETWEEN 1 AND 10  
  16. [‘not between’, ‘id’, 1, 10] //not id BETWEEN 1 AND 10  
  17. [‘in’, ‘id’, [1, 2, 3]] //id IN (1, 2, 3)  
  18. [‘not in’, ‘id’, [1, 2, 3]] //not id IN (1, 2, 3)  
  19. [‘like’, ‘name’, ‘tester’] //name LIKE ‘%tester%’  
  20. [‘like’, ‘name’, [‘test’, ‘sample’]] //name LIKE ‘%test%’ AND name LIKE ‘%sample%’  
  21. [‘not like’, ‘name’, [‘or’, ‘test’, ‘sample’]] //not name LIKE ‘%test%’ OR not name LIKE ‘%sample%’  
  22. [‘exists’,’id’, $userQuery] //EXISTS (sub-query) | not exists  
  23. [‘>’, ‘age’, 10] //age>10  

 

ADD WHERE

 

[php] view
plain
copy

 

金沙注册送58 9金沙注册送58 10

  1. $status = 10;  
  2. $search = ‘yii’;  
  3. $query->where([‘status’ => $status]);  
  4. if (!empty($search)) {  
  5.     $query->andWhere([‘like’, ‘title’, $search]);  
  6. }  
  7. //WHERE (`status` = 10) AND (`title` LIKE ‘%yii%’)  
  8. //andWhere() or orWhere()  

 

 

FILTER WHERE

 

[php] view
plain
copy

 

金沙注册送58 11金沙注册送58 12

  1. $query->filterWhere([Mysql使用自定义方法以及cakeph,则把字段B相加。  
  2.     ‘username’ => $username,  
  3.     ’email’ => $email,  
  4. ]);  
  5. //如果email为空,则 WHERE username=:username  

 

ORDER BY

[php] view
plain
copy

 

金沙注册送58 13金沙注册送58 14

  1. $query->orderBy([  
  2.     ‘id’ => SORT_ASC,  
  3.     ‘name’ => SORT_DESC,  
  4. ]);  
  5. //orderBy , addOrderBy  

 

GROUP BY

[php] view
plain
copy

 

金沙注册送58 15金沙注册送58 16

  1. $query->groupBy(‘id, status’);  
  2. $query->addGroupBy([‘created_at’, ‘updated_at’]);  

 

HAVING 

 

[php] view
plain
copy

 

金沙注册送58 17金沙注册送58 18

  1. $query->having([‘status’ => $status]);  
  2. //having,andHaving,orHaving  

 

LIMIT OR OFFSET

 

[php] view
plain
copy

 

金沙注册送58 19金沙注册送58 20

  1. $query->limit(10);  
  2. $query->offset(10);  

 

JOIN

 

  • innerJoin()
  • leftJoin()
  • rightJoin()

[php] view
plain
copy

 

金沙注册送58 21金沙注册送58 22

  1. $query->select([‘user.name AS author’, ‘post.title as title’])  
  2.     ->from(‘user’)  
  3.     ->leftJoin(‘post’, ‘post.user_id = user.id’);  
  4. $query->join(‘FULL OUTER JOIN’, ‘post’, ‘post.user_id = user.id’);  
  5. $query->leftJoin([‘u’ => $subQuery], ‘u.id=author_id’);  

 

 

UNION

[php] view
plain
copy

 

金沙注册送58 23金沙注册送58 24

  1. $query = new Query();  
  2. $query->select(“id, category_id as type, name”)->from(‘post’)->limit(10);  
  3. $anotherQuery = new Query();  
  4. $anotherQuery->select(‘id, type, name’)->from(‘user’)->limit(10);  
  5. $query->union($anotherQuery);  

 

QUERY METHODS

  • all() //全体行列
  • one() //第一行
  • column() //第一列
  • scalar() //第一行第一列
  • exists() //是或不是有结果存在
  • count() //记录数量
  • sum($q), average($q), max($q), min($q) //$q 为字段或表明式

[php] view
plain
copy

 

金沙注册送58 25金沙注册送58 26

  1. $count = (new \yii\db\Query())  
  2.     ->from(‘user’)  
  3.     ->where([‘last_name’ => ‘Smith’])  
  4.     ->count();  
  5. //SELECT COUNT(*) FROM `user` WHERE `last_name`=:last_name  
  6.   
  7.   
  8. $command = (new \yii\db\Query())  
  9.     ->select([‘id’, ’email’])  
  10.     ->from(‘user’)  
  11.     ->where([‘last_name’ => ‘Smith’])  
  12.     ->limit(10)  
  13.     ->createCommand();  
  14.       
  15. // show the SQL statement  
  16. echo $command->sql;  
  17. // show the parameters to be bound  
  18. print_r($command->params);  
  19.   
  20.   
  21. // returns all rows of the query result  
  22. $rows = $command->queryAll();  

 

QUERY RESULTS

 

[php] view
plain
copy

 

金沙注册送58 27金沙注册送58 28

  1. use yii\db\Query;  
  2. $query = (new Query())  
  3.     ->from(‘user’)  
  4.     ->indexBy(‘username’);  
  5. foreach ($query->batch() as $users) {  
  6.     // $users is indexed by the “username” column  
  7. }  
  8. foreach ($query->each() as $username => $user) {  
  9. }  

 

 

INDEXING

 

[php] view
plain
copy

 

金沙注册送58 29金沙注册送58 30

  1. use yii\db\Query;  
  2. $query = (new Query())  
  3.     ->from(‘user’)  
  4.     ->orderBy(‘id’);  
  5. foreach ($query->batch() as $users) {  
  6.     // batch( $batchSize = 100, $db = null )  
  7.     // 贰个批次取100行  
  8. }  
  9. foreach ($query->each() as $user) {  
  10.     // 一行一行取  
  11. }  

 

转载于:

 

 

 

金沙注册送58 31

 

 

落到实处代码:

第一步:设置SET GLOBAL log_bin_trust_function_creators=TRUE;

第一步:设置SET GLOBAL log_bin_trust_function_creators=TRUE;

$Model = D(‘Model’);
$res =$Model->query(“SELECT user_id,sum(money) from tr_order GROUP
BY user_id”);

如果报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)这种张冠李戴

如果报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)这种不当

结果:

  www.2cto.com  

  www.2cto.com  

array (size=2)
  0 =>
    array (size=2)
      ‘user_id’ => string ’72’ (length=2)
      ‘sum(money)’ => string ‘100677.00’ (length=9)
  1 =>
    array (size=2)
      ‘user_id’ => string ’89’ (length=2)
      ‘sum(money)’ => string ‘34.00’ (length=5)

第二步:

第二步:

 

Sql代码  

Sql代码  

手艺扩张:

DELIMITER $$  

DELIMITER $$  

行使ThinkPHP提供的query函数,能够直接输入我们的sql语句。

  

  

GROUP BY 按user_id分组。

USE `zhiku`$$  

USE `zhiku`$$  

sum()函数:并对的money字段值实行增加。

  

  


DROP FUNCTION IF EXISTS `getChildDept`$$  

DROP FUNCTION IF EXISTS `getChildDept`$$  

  

  

CREATE  FUNCTION `getChildDept`(rootId INT) RETURNS TEXT CHARSET utf8
 

CREATE  FUNCTION `getChildDept`(rootId INT) RETURNS TEXT CHARSET utf8
 

BEGIN  

BEGIN  

    DECLARE sTemp VARCHAR(1000);  

金沙注册送58,    DECLARE sTemp VARCHAR(1000);  

    DECLARE sTempChd VARCHAR(1000);  

    DECLARE sTempChd VARCHAR(1000);  

    SET sTemp = ‘$’;  

    SET sTemp = ‘$’;  

    SET sTempChd =CAST(rootId AS CHAR);  

    SET sTempChd =CAST(rootId AS CHAR);  

    WHILE sTempChd IS NOT NULL DO  

    WHILE sTempChd IS NOT NULL DO  

        SET sTemp = CONCAT(sTemp,’,’,sTempChd);  

        SET sTemp = CONCAT(sTemp,’,’,sTempChd);  

        SELECT GROUP_CONCAT(id) INTO sTempChd FROM zk_departments
WHERE FIND_IN_SET(parent_id,sTempChd)>0;  

        SELECT GROUP_CONCAT(id) INTO sTempChd FROM zk_departments
WHERE FIND_IN_SET(parent_id,sTempChd)>0;  

    END WHILE;  

    END WHILE;  

    RETURN sTemp;  

    RETURN sTemp;  

    END$$  

    END$$  

DELIMITER ;  

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;

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为:

放在cakephp为:

Php代码  

Php代码  

$conditions = array(‘FIND_IN_SET(dept_id,
getChildDept(‘.$dept_id.’))’);  

$conditions = array(‘FIND_IN_SET(dept_id,
getChildDept(‘.$dept_id.’))’);  

            $condition_join = ‘`User`.`id` =
`UserDepartment`.`user_id`’;  

            $condition_join = ‘`User`.`id` =
`UserDepartment`.`user_id`’;  

            if(!emptyempty($c))$condition_join  .= ‘ AND
INSTR(User.pinyin,”‘.$c.'”)=2’;  

            if(!emptyempty($c))$condition_join  .= ‘ AND
INSTR(User.pinyin,”‘.$c.'”)=2’;  

            //分页  

            //分页  

            $this->paginate = array(  

            $this->paginate = array(  

                    ‘UserDepartment’ => array(  

                    ‘UserDepartment’ => array(  

                            ‘conditions’ => $conditions,  

                            ‘conditions’ => $conditions,  

                            ‘order’      =>
array(‘dept_id’=>’ASC’),  

                            ‘order’      =>
array(‘dept_id’=>’ASC’),  

                            ‘limit’      => 10,  

                            ‘limit’      => 10,  

                            ‘recursive’  => -1,  

                            ‘recursive’  => -1,  

                            ‘group’      => array(‘user_id’),  

                            ‘group’      => array(‘user_id’),  

                            ‘fields’     =>
array(‘user_id’,’dept_id’),  

                            ‘fields’     =>
array(‘user_id’,’dept_id’),  

                            ‘joins’      => array(array(  

                            ‘joins’      => array(array(  

                                                 ‘alias’ => ‘User’,  

                                                 ‘alias’ => ‘User’,  

                                                 ‘table’ =>
‘zk_users’,  

                                                 ‘table’ =>
‘zk_users’,  

                                                 ‘type’ => ‘INNER’,  

                                                 ‘type’ => ‘INNER’,  

                                                 ‘conditions’ =>
$condition_join,  

                                                 ‘conditions’ =>
$condition_join,  

                                            )),  

                                            )),  

                    )  

                    )  

            );  

            );  

            $data = $this->paginate(‘UserDepartment’);  

            $data = $this->paginate(‘UserDepartment’);  

 

 

第一步:设置SET GLOBAL log_bin_trust_function_creators=TRUE;
如果报ERROR 1418 (HY000): This function…

相关文章

网站地图xml地图