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.
