In an effort to make writing plain SQLs easier, I am thinking to add the query builder feature to Yii, like other frameworks do. Below is my design. Would you please help review it and leave your comments? Thanks. (Note, this will be implemented in 1.1.6, not 1.1.5).
An example usage:
$db->createCommand()
->select('name, password')
->from('tbl_user')
->where('id=:id', array(':id'=>$id))
->queryRow(); // or queryAll, queryScalar, queryColumn. Calling getText() will return the complete SQL
As you can see, the query builder will be built on top of CDbCommand.
Below is the complete spec for the query builder. Note that currently, it only supports SELECT, INSERT, UPDATE and DELETE SQLs.
select($columns)
Specifies the columns to be selected.
-
select(): equivalent to select(’*’), selecting all columns
-
select(‘id, name’): column names will be properly quoted unless there is parenthesis, apply to all examples below
-
select(array(‘id’, ‘name’)): equivalent to above
-
select(‘u.id as uid’): with prefix and alias
function from($tables)
Specifies the tables to be selected from.
-
from(‘tbl_user’): table name will be properly quoted unless there is parenthesis, apply to all examples below
-
from(‘tbl_user u’): with table alias
-
from(‘tbl_user, tbl_profile’): from multiple tables,
-
from(array(‘tbl_user’, ‘tbl_profile’)): equivalent to above
-
from(’(select * from tbl_profile) p’): from a sub-query (note the parenthesis is required to avoid quoting)
function where($conditions, $params=array())
Specifies the conditions to be put in the WHERE clause as well as the parameters to be bound.
Nested arrays can be used to specify the conditions. Within each array, the first element must
be an operator (shown as follows). The parenthesis will be added according to the way the arrays are nested.
-
where(‘id=1 or id=2’): a simple condition
-
where(‘id=:id1 or id=:id2’, array(’:id1’=>$id1, ‘:id2’=>$id2)): with parameters
The following shows the operators available when using arrays to specify conditions
-
where(array(‘or’, ‘id=1’, ‘id=2’)): same as “id=1 or id=2”
-
where(array(‘and’, id=1’, array(‘or’, ‘id=2’, ‘id=3’))): same as “id=1 and (id=2 or id=3)”
-
where(array(‘in’, ‘id’, array(1,2)): same as “id in (1,2)”
-
where(array(‘not in’, ‘id’, array(1,2))): same as “id not in (1,2)”
-
where(array(‘like’, ‘name’, ‘John’)): same as “name like ‘%John%’”
-
where(array(‘like’, ‘name’, array(‘John’, ‘Shin’))): same as “name like ‘%John%’ and name like ‘%Shin%’”
-
where(array(‘or like’, ‘name’, array(‘John’, ‘Shin’))): same as “name like ‘%John%’ or name like ‘%Shin%’”
-
where(array(‘not like’, ‘name’, ‘John’)): same as “name not like ‘%John%’”
-
where(array(‘or not like’, ‘name’, array(‘John’, ‘Shin’))): same as “name not like ‘%John%’ or name not like ‘%Shin%’”
function join($table, $on, $params=array())
Specifies how to join with a table. This is similar to where() except that it has the $table parameter.
- join(‘tbl_profile’, ‘profile_id=user_id’): inner join with tbl_profile. The table name will be properly quoted unless there is parenthesis.
Besides inner join, the following functions will also be provided with the same function signature:
-
leftJoin
-
rightJoin
-
fullJoin
-
naturalJoin
function groupBy($columns)
Specifies the GROUP BY clause. The usage is the same as select().
function having($conditions, $params=array())
Specifies the HAVING clause. The usage is the same as where().
function orderBy($columns)
Specifies the ORDER BY clause. The usage is the same as select().
- orderBy(‘name, rating DESC’)
function limit($limit, $offset)
Specifies the LIMIT clause.
function insert($table, $columns)
Specifies an INSERT SQL.
- insert(‘tbl_user’, array(‘id’=>1, ‘name’=>‘John’)): table and column names will be properly quoted
function update($table, $columns, $conditions=’’, $params=array())
Specifies an UPDATE SQL.
The ($conditions, $params) part is the same as in where().
function delete($table, $conditions=’’, $params=array())
Specifies a DELETE SQL.
The ($conditions, $params) part is the same as in where().
(which i was missing from PRADO).
