Query builder

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:


   	->select('name, password')


   	->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.


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().

Nice idea… very clean and simple… I think that for most of users it will be easier to understand and use then the CDbCriteria, at least for the simpler queryes

I’like this way… But could you add an example of inner joins requests or some more complicated queries

Here you are:


        ->select('name, password, tbl_profile.content')


        ->join('tbl_profile', 'tbl_profile.user_id=tbl_user.id')

        ->where('id=:id', array(':id'=>$id))


Basically, you need to make use of the above given APIs to construct SQLs. For general SQLs (easy or complex), I think this is sufficient.

Please help me find out anything missing.

I just found one: select distinct.

Interesting approach. At first i wondered, why would someone want to use this if you could use plain SQL instead? But this way it’s much easier to parameterize complex queries. Could in fact replace my custom simple SQLMap implementation :) (which i was missing from PRADO).

Maybe one thing as alternative for aliases:

  • select(array(‘table.column1’=>‘alias1’, ‘table.column2’=>‘alias2’))

And what’s the problem with SELECT DISTINCT?

Oh, and can something like this be reproduced with the command builder, too?

SELECT IF(vp2.VALUE>0, FLOOR(100*(1-vp.VALUE/vp2.VALUE)), 0) AS PRICECUT ...

In my humble opinion, if you allow comments from framework newbies like me, I think that creating such approaches is what we say in Spain ‘rizar el rizo’ and I think that for the sake of simplicity, if that is your aim, SQL statements should be viewed at its full nature when using queries outside of AR objects.

Its encapsulation tend to create confusion with new comers and I see it in lots of questions and problems at the forum that require the vision of the entire SQL query to find out the problems they face.

Also, the learning curve of such object will be bigger for them, don’t you think? I love the way it is right now… easy and clean. Other frameworks, tend to put far too many features that at the end… it is far too much for what the programmer actually requires, too many ways of doing the same things.

What could be simpler than this:

Yii::app()->db->createCommand(‘SELECT * FROM tbl_bogus’)->query();

Nevertheless, this is just my personal opinion.

I think it’s a great thing. :)

If only SQL was as easy as SELECT * FROM tbl_bogus ! :lol:

But it isn’t.

Most of the time we would use AR, but in those situations where we want to be lean and mean, it would help to have a good query building language.

N00bs should use active record objects, and models, shouldn’t they?

Correct me, if I’m wrong. ;)

Hey jacmoe, don’t take that SQL as an example :)

I just displayed a silly SQL statement to prove how easy is to review it if something goes wrong. About what you are saying tell me, if you don’t know SQL how would you use those functions at all? You need to know it no matter what… Dont you think so?

And I don’t say is a bad idea…

Particullary, I would want to see something more like CDbCriteria (maybe a "CDbQuery")

$query = new CDbQuery("tbl_user");

$query->alias = 'user';

$query->with('tbl_profile', 'tbl_profile.user_id=tbl_user.id');

$query->select = 'user.name, user.password, tbl_profile.content';

$query->order = 'name';

$result = $query->queryRow(); 

I think that using an object (just like CDbCriteria) is easier to customizate.

Knowing SQL is surely a prerequisite for using the query builder. The nice thing is, that the code pretty much resembles the underlying SQL but like i said allows much more flexibility . Instead of concatenating strings or imploding Arrays of optional AND conditions again and again to build your complex SQL statement "by hand" the builder wraps this job up for you.

good idea… Good Luck Yii Dev Team :)

This is a great idea. I dislike CDbCriteria and avoid it, so this would be a terrific addition.

(Shouldn’t returning the complete SQL be done with toText() instead of getText(). Oh, I see, CDbCommand already has getText(), never noticed before.)

I thought this too, so I support this syntax also.

A CDbQueryBuilder does make sense, as you then encapsulate the building of the query separately from executing it. It does mean that CDbCommand and CDbQueryBuilder responsibilities are loosely coupled. You could then simplify the creation of CDbCommands using an interface, this allows creation of other ways to build queries that CDbCommand can use.

CDbCommand::__construct( CDbConnection $connection, (string|CIDbQuery) $query );

I agree with this. It’s like SQL but it encapsulates the quoting etc. Reducing the chance of errors and a rushed introduction of a security hole such as SQL injection.

For a SQL query like this you can use it just like this… but don’t forget that not all queries are typed directly in the code… many of them are calculated or constructed… for that cases a query builder is suited better, IMHO

Can this also be used with find() / findAll() methods?

@gstar find() and findAll() are from CActiveRecord. Here you get the query…() methods of CDbCommand.

I like what Say_Ten says, +1. There is definitely a case for separating the query from the execution, but that doesn’t mean breaking the original syntax. Being able to avoid the CDbCriteria-style syntax is key for me. By all means let folk do that, though, then

You are right, but isn’t that the function of CDbCriteria class?

Anyway, if it is just another approach to make things better and easier to understand, or a new way of doing things that ‘plug’ into the current system that’s OK; it is not a bad approach as other frameworks did it already and seems to be quite accepted.

I am not against this idea, it was just an opinion.

I think the former way (directly) is easier to read. But using CDbCriteria way is easier to setup.

EDIT: Changed my mind. I think latter is easier to understand too, in cases of medium/complex queries.

I like the idea…