Adding condition to your DSQL

Calling $q->where() method will narrow down the returned data-set by applying additional condition. Method, however, accepts many different formats of arguments.

Basic Usage: where(string, primitive)

The basic rule is that the first argument is a string. The second argument is a primitive type (string, number) and will be automatically converted into parameter.

$q->where('id',1);        // where id=:a    'a'=>1
$q->where('id>',1);       // where id>:a    'a'=>1
$q->where('id!=',1);      // where id!=:a   'a'=>1
$q->where('id like',1);   // where id like :a   'a'=>1
$q->where('id in',array(1,2));      // where id in(:a,:b)   'a'=>1, 'b'=>2

If the second argument is "null" then operation "is null" is used automatically. Several ways to call it can be used.

$q->where('id',null);       // where id is NULL
$q->where('id is',null);    // where id is NULL
$q->where('id!=',null);     // where id is NOT NULL
$q->where('id is not',null); // where id is NOT NULL

Using with Expressions: expr()

You may use $q->expr() if you are willing to insert expressions.

// Single argument mode

// Using operator with the first argument
$q->where('date>',$q->expr('DATE_SUB(CURDATE(), INTERVAL 2 MONTH)');

// Expression may contain parameters. Unlike where('id',1) this will not use equation operator
$q->where('age',$q->expr('between :left and :right')->param(array('left'=>$l'right'=>$r)));

// both arguments may be expressions
$q->where($this->expr('length(password)'),$q->expr('between 3 and 10'));

// Alternative way to specify parameter

Please avoid use of param(), because it may result in the clash, sub-query uses same params as a master query.

AND conditions: where(..)->where()

Calling where() multiple times will require all of the conditions to be met. Using "AND" operator.

OR conditions: where(array)

Callng where() with a single array argument will use OR to join those conditions. The same principles apply on the array as no the actual where() call. You can even specify arrays recursively

'id',1),  // where (id=:a or id=:b)  array('a'=>1, 'b'=>2)

$q->expr('a=b'))    // where (len(name)>:a or a=b)  array('a'=>5)

There is alternative way to use OR conditions. Use whichever you like more. or() method relies on expr() to produce a new query.

$q->where$q->or()->where('a',1)->where('b>',5) );


You may use $q->dsql() as a quick way to produce sub-queries. Calling this method will create a new DSQL object, which you can use similarly as expression.

where('book_id'$q->dsql()->table('book')->where('is_rented','Y') );   // by default "id" field is used.
  // produces: select name from author where book_id in (select id from book where is_rented=:a)    array('a'=>'Y')
  // Note: This is quite ineffective way for listing all authors who's books are rented

// produces:  select name from author where (select count(*) from book where>5
  // Displays names of authors who have more than 5 books.