复杂查询

cakephp的复杂查询可以参考之前的文档和demo

此外还可以直接使用sql语句进行查询，以此项目为例子：

访问/users接口，使用cakephp本身的复杂查询方式结果是这样的：

{"code":200,"status":"success","body":[{"id":1,"loginId":"test","phone":"1234567890123","name":"123","avatar":null,"lastLoginTime":null,"mustChangePassword":null,"status":1,"remarks":null,"addresses":[{"id":1,"userId":1,"address":"1","areas":[{"id":1000,"parentId":null,"name":"江苏省","children":[]}]}],"roles":[{"id":1,"name":"默认","status":0,"remarks":null,"permissions":[{"id":1200,"parentId":1000,"code":"menu-info-company","name":"","method":"GET","url":null,"remarks":null},{"id":1120,"parentId":1100,"code":"info-carrier-user-edit","name":"","method":"POST","url":null,"remarks":null},{"id":1220,"parentId":1200,"code":"info-company-user-edit","name":"","method":"POST","url":null,"remarks":null},{"id":1210,"parentId":1200,"code":"info-company-edit","name":"","method":"POST","url":null,"remarks":null},{"id":1000,"parentId":null,"code":"menu-info","name":"","method":"","url":null,"remarks":null},{"id":1110,"parentId":1100,"code":"info-carrier-edit","name":"","method":"POST","url":null,"remarks":null},{"id":1100,"parentId":1000,"code":"menu-info-carrier","name":"","method":"GET","url":null,"remarks":null},{"id":1300,"parentId":1000,"code":"menu-info-company-migrate","name":"","method":"GET","url":null,"remarks":null},{"id":1230,"parentId":1200,"code":"companies-declare-edit","name":"","method":"POST","url":null,"remarks":null}]}]}]}

把这个修改为直接使用sql进行查询
修改src/Model/Logic/UsersLogic.php中的findAll方法：

    public function findAll()
    {
        $conn = $this->usersTable->getConnection();  // 获取数据库连接
        // 直接执行sql然后取得结果
        $res = $conn->execute('
            SELECT
            users.*,
            (select string_agg(permission_id||\'\' , \',\') from roles_permissions) as permissions,
            (select string_agg(areas.name||\'\' , \',\') from areas) as areas
            FROM users
            LEFT JOIN users_roles ON users."id" = users_roles.user_id
            LEFT JOIN roles ON users_roles.role_id = roles."id"
            LEFT JOIN roles_permissions ON roles."id" = roles_permissions.role_id
            LEFT JOIN addresses ON users."id" = addresses.user_id
            LEFT JOIN addresses_areas ON addresses."id" = addresses_areas.address_id
            LEFT JOIN areas on areas."id" = addresses_areas.area_id
            GROUP BY users.id')
        ->fetchAll('assoc');

        return $this->underscoreToCamel($res);
    }

访问api的结果：

{"code":200,"status":"success","body":[{"id":1,"loginId":"test","phone":"1234567890123","password":"123456","name":"123","avatar":null,"lastLoginTime":null,"mustChangePassword":null,"status":1,"createdBy":null,"createdTime":null,"modifiedBy":null,"modifiedTime":null,"remarks":null,"permissions":"1200,1120,1220,1210,1000,1110,1100,1210,1300,1000,1300,1230,1230,1300,1220,1100,1120,1200,1100,1110,1230,1000,1220,1120,1110,1210,1200","areas":"江苏省,常州市,新北区,武进区"}]}

这种方式由于sql本身的限制无法让一条数据中包含数组的内容，所以两种方式的结果会不一样
但是如果有情况需要很复杂的查询时，可以直接用sql来处理会简单很多