視圖查詢

視圖查詢可以實現(xiàn)不依賴數(shù)據(jù)庫視圖的多表查詢，并不需要數(shù)據(jù)庫支持視圖，例如：

Db::view('User','id,name')
    ->view('Profile','truename,phone,email','Profile.user_id=User.id')
    ->view('Score','score','Score.user_id=Profile.id')
    ->where('score','>',80)
    ->select();

生成的SQL語句類似于：

SELECT User.id,User.name,Profile.truename,Profile.phone,Profile.email,Score.score FROM think_user User INNER JOIN think_profile Profile ON Profile.user_id=User.id INNER JOIN think_socre Score ON Score.user_id=Profile.id WHERE Score.score > 80

注意，視圖查詢無需調(diào)用table和join方法，并且在調(diào)用where和order方法的時候只需要使用字段名而不需要加表名。

默認使用INNER join查詢，如果需要更改，可以使用：

Db::view('User','id,name')
    ->view('Profile','truename,phone,email','Profile.user_id=User.id','LEFT')
    ->view('Score','score','Score.user_id=Profile.id','RIGHT')
    ->where('score','>',80)
    ->select();

生成的SQL語句類似于：

SELECT User.id,User.name,Profile.truename,Profile.phone,Profile.email,Score.score FROM think_user User LEFT JOIN think_profile Profile ON Profile.user_id=User.id RIGHT JOIN think_socre Score ON Score.user_id=Profile.id WHERE Score.score > 80

可以使用別名：

Db::view('User',['id'=>'uid','name'=>'account'])
    ->view('Profile','truename,phone,email','Profile.user_id=User.id')
    ->view('Score','score','Score.user_id=Profile.id')
    ->where('score','>',80)
    ->select();

生成的SQL語句變成：

SELECT User.id AS uid,User.name AS account,Profile.truename,Profile.phone,Profile.email,Score.score FROM think_user User INNER JOIN think_profile Profile ON Profile.user_id=User.id INNER JOIN think_socre Score ON Score.user_id=Profile.id WHERE Score.score > 80

可以使用數(shù)組的方式定義表名以及別名，例如：

Db::view(['think_user'=>'member'],['id'=>'uid','name'=>'account'])
    ->view('Profile','truename,phone,email','Profile.user_id=member.id')
    ->view('Score','score','Score.user_id=Profile.id')
    ->where('score','>',80)
    ->select();

生成的SQL語句變成：

SELECT member.id AS uid,member.name AS account,Profile.truename,Profile.phone,Profile.email,Score.score FROM think