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
$q->where($q->expr('a=b'));

// 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
$q->where($this->expr('length(password)'),'>',5);

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

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

$q->where(array(
  array(
$q->expr('len(name)'),'>',5),
  array(
$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) );

Subqueries

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.

$q
 
->table('author')
 ->
field('name')
 ->
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

$q
  
->table('author')
  ->
field('name')
  ->
where(
    
$q->dsql()
      ->
table('book')
      ->
where('author_id'$q->getField('id'))
      ->
field('count(*)'),
    
'>',5);    
  
// produces:  select name from author where (select count(*) from book where author_id=author.id)>5
  // Displays names of authors who have more than 5 books.