# 多表查询 多表查询 ~~~ <?php namespace Action; use HY\Action; class Index extends Action { public function Index(){ //实例User表为对象 $User = S("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 } } ~~~ 举一个通俗易懂的例子 两个表 post 与 user post表的数据 | id | uid | title | | --- | --- | --- | | 1 | 1 | 文章标题 | | 2 | 1 | 文章标题 | | 3 | 1 | 文章标题 | user表的数据 | uid | username | | --- | --- | | 1 | admin | | 2 | xxxx | | 3 | dddd | 问题: 如果获取 post表数据的时候 同时获取 用户名 (user.username) 答: 简单的都是先获取了 post的数据出来 再循环user表中的username 我们取出post表的数据时 还能取出uid 用户ID 却不能取出用户名, 这时就能用到多表查询 ~~~ S('Post')->select(array( "[>]user" => [ "uid" => "uid"], //post.uid == user.uid ),array( 'post.title', 'user.username' ) ); 输出: arrary( 'title'=>'文章标题', 'username'=>'admin' ) ~~~ 多表查询 Count ~~~ S('Post')->count(array( "[>]user" => [ "uid" => "uid"], //post.uid == user.uid ), '*' ); ~~~ 复杂的多表查询 Count (HYBBS处的一段搜索代码) ~~~ $page_count = $Thread->count( array( "[>]post" => [ "pid" => "id"], //post.id == thread.pid ), '*', array('AND'=>array( 'isthread'=>1 ,'OR'=>array( 'thread.title[~]'=>$key, 'post.content[~]'=>$key ))) ); ~~~ OBJ->count($join, "*" ,条件 );