Yii createCommand queryAll

I posted here examples of createCommand Query with properties. createCommand Properties are distinct, from, group, having, join, limit, offset, params, pdoStatement, select and where.
CDbCommand represents an SQL statement to execute against a database. bindParam used to bind a php variable to parameter in SQL query. bindValue is used to bind a value to and SQL Parameter with automatic prepared

Functions

$connection=Yii::app()->db;   
$command=$connection->createCommand();
$dataReader=$command->query();   // execute a query SQL
$rows=$command->queryAll();      // all rows of result
$row=$command->queryRow();       // the first row of result
$column=$command->queryColumn(); // first column of result
$value=$command->queryScalar();  // first field in the first row of result

text, bindParam, execute

$connection=Yii::app()->db;
$sql="SELECT * FROM tbl_user WHERE user_id=':uid'";
$command=$connection->createCommand();
$command->text=$sql;
$command->bindParam(':uid',$userid, PDO::PARAM_INT);
$command->execute();

Output

SELECT * FROM tbl_user WHERE user_id=':uid'

from, select, where, queryrow

 
$user = Yii::app()->db->createCommand()
        ->select('username, password')
        ->from('tbl_user')
        ->where('user_id=:id', array(':id'=>1)) //where($condition, $params)   
        ->queryRow();

bindValue

$user = Yii::app()->db->createCommand()
        ->select('username, password')
        ->from('tbl_user')
        ->where('user_id=:id') //where($condition, $params)
        ->bindValue(":id", 1, PDO::PARAM_INT);   
        ->queryRow();

Output

SELECT 'user_name', 'password' FROM 'tbl_user' WHERE user_id=:id

limit, queryAll

$user = Yii::app()->db->createCommand()
        ->select('username, password')
        ->from('tbl_user')
        ->where('user_id=:id', array(':id'=>1))
        ->limit(1)    
        ->queryAll();

Output

SELECT `user_name`, `password` FROM `tbl_user` WHERE user_id=:id LIMIT 1

limit, offset, order

$user = Yii::app()->db->createCommand()
        ->select('user_id,username, user_type')
        ->from('tbl_user')
        ->limit(10)
        ->offset(2)
        ->order(user_id)    
        ->queryAll();

Output

SELECT `user_id`, `user_name`, `usertype` FROM `tbl_user` ORDER BY `user_id` LIMIT 10 OFFSET 2;

DISTINCT

$user = Yii::app()->db->createCommand()
        ->select('DISTINCT user_id, user_name')
/**(OR)    
        ->->selectDistinct('user_id, user_name')
**/       
        ->from('tbl_user_commants')
        ->queryAll();    

Output

SELECT DISTINCT `user_id`, `user_name` FROM `tbl_user_commants`

andWhere

 
<?php
$user=Yii::app()->db->createCommand()
        ->select("username, usertype")
        ->from("usermaster")
        ->andWhere("usertype=:id",array(':id'=>1))
        ->andWhere("isactive=:status",array(':status'=>1))
        ->queryAll();
?>

Output

SELECT `username`, `usertype` FROM `usermaster` WHERE (usertype=:id) AND (isactive=:status)

orWhere

 
<?php
$user=Yii::app()->db->createCommand()
        ->select("username, usertype")
        ->from("usermaster")
        ->andWhere("usertype=:id",array(':id'=>1))
        ->orWhere("isactive=:status",array(':status'=>1))
        ->queryAll();
?>

Output

SELECT `username`, `usertype` FROM `usermaster` WHERE (usertype=:id) OR (isactive=:status)

group, having

$user = Yii::app()->db->createCommand()
        ->select('user_id, user_name,user_type')
        ->from('tbl_user_commants')
        ->group('user_type')    
        ->having(count(commants_id)>5)
        ->queryAll();

join

$user = Yii::app()->db->createCommand()
        ->join('tbl_user tu')
        ->select('user_id, tu.user_name,tu.user_type, post_id')
        ->from('tbl_user_commants')
        ->group('post_id')    
        ->having(count(commants_id)>2)
        ->queryAll();    

Properties and Type OF CDbCommand

distinct    - boolean
from        - string
group       - string
having      - string
join        - string
limit       - string
offset      - string
order       - string
params      - array
pdoStatement    - PDOStatement
select      - string
text        - string
union       - mixed
where       - string