/**
* @internal
* Build the SQL WHERE clause from the various condition arrays
*
* @param array $cond The condition array, for example
*
* array(
* 'fieldName1' => $value1,
* 'fieldName2 >=' => $value2,
* 'fieldName3 => NULL
* )
*
* @param string $type The condition type "AND" or "OR"; Default is "AND"
*
* @return string The built condition WHERE clause
*/
function db_condition($cond = array(), $type = 'AND')
{
return QueryBuilder::buildCondition($cond, $type);
}
public function testQueryBuilderSELECT()
{
$qb = new QueryBuilder('post', 'p');
$this->assertEqual($qb->getSQL(), 'SELECT `p`.* FROM `post` `p`');
$qb = new QueryBuilder();
$qb->from('post', 'p');
$this->assertEqual($qb->getSQL(), 'SELECT `p`.* FROM `post` `p`');
$qb = db_select('post');
$this->assertEqual($qb->getSQL(), 'SELECT `post`.* FROM `post` `post`');
$qb = db_select('post', 'p');
$this->assertEqual($qb->getSQL(), 'SELECT `p`.* FROM `post` `p`');
$qb = db_select('post', 'p')->fields('p', array('postId', 'postTitle'))->orderBy('p.created', 'desc');
$this->assertEqual($qb->getSQL(), self::oneline('
SELECT `p`.`postId`, `p`.`postTitle` FROM `post` `p`
ORDER BY `p`.`created` DESC
'));
$qb = db_select('post', 'p')->fields('p', array('postId', array('postTitle', 'title')))->orderBy('p.created', 'desc');
$this->assertEqual($qb->getSQL(), self::oneline('
SELECT `p`.`postId`, `p`.`postTitle` `title` FROM `post` `p`
ORDER BY `p`.`created` DESC
'));
$qb = db_select('post', 'p')->fields('p', array('postId', 'postTitle'))->fields('u')->join('user', 'u', 'p.uid = u.uid')->orderBy('p.created', 'desc');
$this->assertEqual($qb->getSQL(), self::oneline('
SELECT `p`.`postId`, `p`.`postTitle`, `u`.* FROM `post` `p`
INNER JOIN `user` `u` ON `p`.`uid` = `u`.`uid`
ORDER BY `p`.`created` DESC
'));
$qb = db_select('post', 'p')->where()->condition('p.postId', 1);
$this->assertEqual($qb->getSQL(), 'SELECT `p`.* FROM `post` `p` WHERE `p`.`postId` = 1');
$qb = db_select('post', 'p')->where()->condition('p.created >', '2015-11-08');
$this->assertEqual($qb->getSQL(), 'SELECT `p`.* FROM `post` `p` WHERE `p`.`created` > "2015-11-08"');
$qb = db_select('post', 'p')->fields('p', array('postId', 'postTitle'))->fields('u', array('fullName', 'username'))->join('user', 'u', 'p.uid = u.uid')->leftJoin('category', 'c', 'p.catId = c.catId')->where()->condition('catId', 1)->condition('uid', 1)->orderBy('p.created', 'desc')->orderBy('c.catId');
$this->assertEqual($qb->getSQL(), self::oneline('
SELECT `p`.`postId`, `p`.`postTitle`, `u`.`fullName`, `u`.`username` FROM `post` `p`
INNER JOIN `user` `u` ON `p`.`uid` = `u`.`uid`
LEFT JOIN `category` `c` ON `p`.`catId` = `c`.`catId`
WHERE `catId` = 1
AND `uid` = 1
ORDER BY `p`.`created` DESC, `c`.`catId` ASC
'));
$qb = db_select('post', 'p')->orWhere()->condition('catId', 1)->condition('catId', 2);
$this->assertEqual($qb->getSQL(), 'SELECT `p`.* FROM `post` `p` WHERE `catId` = 1 OR `catId` = 2');
$qb = db_select('post', 'p')->fields('p')->fields('u', array('fullName', 'username'))->join('user', 'u', 'p.uid = u.uid')->leftJoin('category', 'c', 'p.catId = c.catId')->orWhere(array('postTitle like' => 'A project', db_and(array('postId' => array(1, 2, 3), 'uid' => 1))))->orderBy('p.created', 'desc');
$this->assertEqual($qb->getSQL(), self::oneline('
SELECT `p`.*, `u`.`fullName`, `u`.`username` FROM `post` `p`
INNER JOIN `user` `u` ON `p`.`uid` = `u`.`uid`
LEFT JOIN `category` `c` ON `p`.`catId` = `c`.`catId`
WHERE `postTitle` LIKE "%A project%"
OR ( `postId` IN (1, 2, 3) AND `uid` = 1 )
ORDER BY `p`.`created` DESC
'));
$qb = db_select('post', 'p')->fields('p')->fields('u', array('fullName', 'username'))->join('user', 'u', 'p.uid = u.uid')->join('category', 'c', 'p.catId = c.catId')->where(array('postTitle like' => 'A project', db_or(array('postId' => array(1, 2, 3), 'uid' => 1))))->orderBy('p.created', 'desc')->limit(0, 20);
$this->assertEqual($qb->getSQL(), self::oneline('
SELECT `p`.*, `u`.`fullName`, `u`.`username` FROM `post` `p`
INNER JOIN `user` `u` ON `p`.`uid` = `u`.`uid`
INNER JOIN `category` `c` ON `p`.`catId` = `c`.`catId`
WHERE `postTitle` LIKE "%A project%"
AND ( `postId` IN (1, 2, 3) OR `uid` = 1 )
ORDER BY `p`.`created` DESC
LIMIT 0, 20
'));
$qb = db_select('post', 'p')->fields('p')->fields('u', array('fullName', 'username'))->join('user', 'u', 'p.uid = u.uid')->join('category', 'c', 'p.catId = c.catId')->orWhere(array('postTitle nlike' => 'A project', db_and(array('postId' => array(1, 2, 3), 'postId <=' => 10, db_or(array('created >' => '2014-12-31', 'deleted' => null))))))->orderBy('p.created', 'desc')->limit(5);
$this->assertEqual($qb->getSQL(), self::oneline('
SELECT `p`.*, `u`.`fullName`, `u`.`username` FROM `post` `p`
INNER JOIN `user` `u` ON `p`.`uid` = `u`.`uid`
INNER JOIN `category` `c` ON `p`.`catId` = `c`.`catId`
WHERE `postTitle` NOT LIKE "%A project%"
OR ( `postId` IN (1, 2, 3) AND `postId` <= 10
AND ( `created` > "2014-12-31" OR `deleted` IS NULL )
)
ORDER BY `p`.`created` DESC
LIMIT 5
'));
$qb = db_select('post', 'p')->groupBy('p.catId');
$this->assertEqual($qb->getSQL(), 'SELECT `p`.* FROM `post` `p` GROUP BY `p`.`catId`');
$qb = db_select('post', 'p')->groupBy('p.catId')->having(array('p.catId >' => 10));
$this->assertEqual($qb->getSQL(), 'SELECT `p`.* FROM `post` `p` GROUP BY `p`.`catId` HAVING `p`.`catId` > 10');
$qb = db_select('post', 'p')->groupBy('p.catId')->having(array('p.catId >' => 10));
$this->assertEqual($qb->getSQL(), 'SELECT `p`.* FROM `post` `p` GROUP BY `p`.`catId` HAVING `p`.`catId` > 10');
}
