hyphp框架的多表查询怎么用?

txws2012   ·   发表于 2016-12-31   ·   HYPHP

hyphp框架的多表查询怎么用?按照官方的文档好像实现不了查询的?

假如有两张表cate(id,name,type,mid......)和model(id,name),怎么实现两个表关联查询(cate的mid与model的id)?

按照官方的文档debug输出是SELECT * FROM "he_cate" WHERE "model" IN ('id')

7 条回复   |  直到 2017-1-4 | 2877 次浏览

admin
发表于 2016-12-31

建议用使用->query()进行原生查询  目前框架的多表查询 比较麻烦

评论列表

  • 加载数据中...

编写评论内容

txws2012
发表于 2016-12-31

已用query原生方式查询,就想了解一下框架有没有方式实现

评论列表

  • 加载数据中...

编写评论内容

admin
发表于 2016-12-31

http://php.hyphp.cn/237479

<?php
    namespace Action;
    use HY\Action;
    class IndexAction extends Action {

        public function Index(){
            //实例User表为对象
            $User = M("User");


            // [>] == LEFT JOIN
            // [<] == RIGH JOIN
            // [<>] == FULL JOIN
            // [><] == INNER JOIN

            $User->select("post", array(
                // Here is the table relativity argument that tells the relativity between the table you want to join.

                // The row author_id from table post is equal the row user_id from table account
                "[>]account" => array("author_id" => "user_id"),

                // The row user_id from table post is equal the row user_id from table album.
                // This is a shortcut to declare the relativity if the row name are the same in both table.
                "[>]album" => "user_id",

                // [post.user_id is equal photo.user_id and post.avatar_id is equal photo.avatar_id]
                // Like above, there are two row or more are the same in both table.
                "[>]photo" => array("user_id", "avatar_id"),

                // If you want to join the same table with different value,
                // you have to assign the table with alias.
                "[>]account (replyer)" => array("replyer_id" => "user_id"),

                // You can refer the previous joined table by adding the table name before the column.
                "[>]account" => array("author_id" => "user_id"),
                "[>]album" => array("account.user_id" => "user_id"),

                // Multiple condition
                "[>]account" => array(
                    "author_id" => "user_id",
                    "album.user_id" => "user_id"
                )
            ), array(
                "post.post_id",
                "post.title",
                "account.user_id",
                "account.city",
                "replyer.user_id",
                "replyer.city"
            ), array(
                "post.user_id" => 100,
                "ORDER" => "post.post_id DESC",
                "LIMIT" => 50
            ));

            // SELECT
            //  `post`.`post_id`,
            //  `post`.`title`,
            //  `account`.`city`
            // FROM `post`
            // LEFT JOIN `account` ON `post`.`author_id` = `account`.`user_id`
            // LEFT JOIN `album` USING (`user_id`)
            // LEFT JOIN `photo` USING (`user_id`, `avatar_id`)
            // WHERE
            //  `post`.`user_id` = 100
            // ORDER BY `post`.`post_id` DESC
            // LIMIT 50

        }

    }

自己可以用DEBUG 尝试输出语句 是否正常

评论列表

  • 加载数据中...

编写评论内容

txws2012
发表于 2016-12-31

我就是按照官方的文档来调试的输出的都不符合要求的

评论列表

  • 加载数据中...

编写评论内容

txws2012
发表于 2016-12-31

@admin:回复 #2 建议用使用->query()进行原生查询 目前框架的多表查询比较麻烦
 老大,我知道框架怎么调用不了多表查询了,原来你的Model类里面没有写多表查询的功能,Medoo是支持多表查询的,建议框架Model增加(就在select增加一个表名变量就行了)

    //多表查询,
    public function select_join($table,$join, $columns = null, $where = null){
        return $this->pdo->select($table,$join,$columns,$where);
        
    }


评论列表

  • 加载数据中...

编写评论内容

admin
发表于 2017-1-1

多表查询是写在select的

//原
public function select($join, $columns = null, $where = null){
  return $this->pdo->select($this->table,$join,$columns,$where);
}
//你提供的
public function select_join($table,$join, $columns = null, $where = null){
  return $this->pdo->select($table,$join,$columns,$where);
}

你的函数只是多了$table  

而$table在使用Model的时候已经写到 ->table 了

评论列表

  • 加载数据中...

编写评论内容

txws2012
发表于 2017-1-4

老大,经过测试,把框架的Medoo类库升级到最新版,按照我添加的Model操作类是可以操作多表查询的,其他写法可按照官方的文档可实现了

model

    //多表查询,第一个参数为表名,第二个是关联表名,第三个是查询字段(一定要字段名,*号不行),第四个为where条件
    public function select_join($table,$join, $columns = null, $where = null){
        return $this->pdo->select($table,$join,$columns,$where);   
    }

php操作写法

            $data = S("")->select_join("host",
             array(
                "[>]admin" => array("uid" => "id"),
            ), array("host.domain","admin.username"
            ), array(
                 "ORDER" => "host.id DESC",
                // "LIMIT" => 50
            ));

          var_dump($data);

输出结果

Array
(
    [0] => Array
        (
            [domain] => vsuw.cn
            [username] => admin
        )

    [1] => Array
        (
            [domain] => www.vsuw.cn
            [username] => admin1
        )

)


评论列表

  • 加载数据中...

编写评论内容
登录后才可发表内容

HYBBS © 2016. All Rights Reserved. HYBBS

Powered by HYBBS Version 2.1.3