/**
*
* @dataProvider mergeProvider
*/
public function testMerge($overwriteLimit, $mergeOrderBy, $expectedQuery, $expectedBoundParameters)
{
$this->queryBuilder->from('book', 'b')->select('id')->select('count(*)', 'nb')->join('author', 'a', 'a.id = b.author_id', SelectQueryBuilder::INNER_JOIN)->where('id', 5, SelectQueryBuilder::LESS_THAN)->groupBy('id')->having('score', 5, SelectQueryBuilder::LESS_THAN)->orderBy('price', SelectQueryBuilder::DESC)->limit(20)->offset(10);
$qb = new SelectQueryBuilder();
$qb->from('book')->select('title')->addOption('DISTINCT')->join('editor', 'e', 'e.id = b.editor_id', SelectQueryBuilder::LEFT_JOIN)->_open(SelectQueryBuilder::LOGICAL_OR)->where('title', 'Dune', SelectQueryBuilder::NOT_EQUALS, null)->_close()->groupBy('score', SelectQueryBuilder::ASC)->openHaving(SelectQueryBuilder::LOGICAL_OR)->having('price', 9, SelectQueryBuilder::NOT_EQUALS, null)->closeHaving()->orderBy('score', SelectQueryBuilder::ASC)->limit(50)->offset(0);
$this->queryBuilder->merge($qb, $overwriteLimit, $mergeOrderBy);
$this->assertEquals($expectedQuery, $this->queryBuilder->getQueryString());
$this->assertEquals($expectedBoundParameters, $this->queryBuilder->getBoundParameters());
}
public function getQueryStringProvider()
{
$select1 = new SelectQueryBuilder();
$select1->select('id');
$select1->select('title');
$select1->from('OldBook', 'o');
$select1->where('price', 8, SelectQueryBuilder::GREATER_THAN_OR_EQUAL);
return array(array('', array(), array(), null, array(), '', ''), array('book', array('id', 'title'), array(array(1, 'Dune')), null, array(1, 'Dune'), 'INSERT INTO book (id, title) VALUES (?, ?) ', 'INSERT INTO book (id, title) ' . "\n" . 'VALUES ' . "\n" . '(?, ?) ' . "\n"), array('book', array('id', 'title'), array(array(1, 'Dune'), array(2, 'The Man in the High Castles')), null, array(1, 'Dune', 2, 'The Man in the High Castles'), 'INSERT INTO book (id, title) VALUES (?, ?), (?, ?) ', 'INSERT INTO book (id, title) ' . "\n" . 'VALUES ' . "\n" . '(?, ?), ' . "\n" . '(?, ?) ' . "\n"), array('book', array('id', 'title'), array(), $select1, array(1, 'Dune', 2, 'The Man in the High Castles'), 'INSERT INTO book (id, title) SELECT id, title FROM OldBook AS o WHERE price >= ? ', 'INSERT INTO book (id, title) ' . "\n" . 'SELECT id, title ' . "\n" . 'FROM OldBook AS o ' . "\n" . 'WHERE price >= ? ' . "\n"), array('book', array('id', 'title'), array(array(3, 'Do Androids Dream of Electric Sheep?')), $select1, array(1, 'Dune', 2, 'The Man in the High Castles'), 'INSERT INTO book (id, title) SELECT id, title FROM OldBook AS o WHERE price >= ? ', 'INSERT INTO book (id, title) ' . "\n" . 'SELECT id, title ' . "\n" . 'FROM OldBook AS o ' . "\n" . 'WHERE price >= ? ' . "\n"));
}
public function getWhereStringProvider()
{
$subquery1 = new SelectQueryBuilder();
$subquery1->select('id')->from('author')->where('last_name', '%Her%', WhereQueryBuilder::LIKE);
$subquery2 = new SelectQueryBuilder();
$subquery2->from('author')->where('first_name', '%Ph%', WhereQueryBuilder::LIKE);
return array(array(array(array('id', 1, null, null)), 'WHERE id = ? ', 'WHERE id = ? ' . "\n", array(1)), array(array(array('id', 1, WhereQueryBuilder::NOT_EQUALS, null)), 'WHERE id != ? ', 'WHERE id != ? ' . "\n", array(1)), array(array(array('published_at', null, WhereQueryBuilder::IS_NULL, null)), 'WHERE published_at IS NULL ', 'WHERE published_at IS NULL ' . "\n", array()), array(array(array('published_at', null, WhereQueryBuilder::IS_NOT_NULL, null)), 'WHERE published_at IS NOT NULL ', 'WHERE published_at IS NOT NULL ' . "\n", array()), array(array(array('score', array(8, 15), WhereQueryBuilder::BETWEEN, null)), 'WHERE score BETWEEN ? AND ? ', 'WHERE score BETWEEN ? AND ? ' . "\n", array(8, 15)), array(array(array('score', array(8, 15), WhereQueryBuilder::NOT_BETWEEN, null)), 'WHERE score NOT BETWEEN ? AND ? ', 'WHERE score NOT BETWEEN ? AND ? ' . "\n", array(8, 15)), array(array(array('score', array(8, 12, 10, 9, 15), WhereQueryBuilder::IN, null)), 'WHERE score IN (?, ?, ?, ?, ?) ', 'WHERE score IN (?, ?, ?, ?, ?) ' . "\n", array(8, 12, 10, 9, 15)), array(array(array('score', array(8, 12, 10, 9, 15), WhereQueryBuilder::NOT_IN, null)), 'WHERE score NOT IN (?, ?, ?, ?, ?) ', 'WHERE score NOT IN (?, ?, ?, ?, ?) ' . "\n", array(8, 12, 10, 9, 15)), array(array(array('id', 1, WhereQueryBuilder::NOT_EQUALS, null), array('score', array(8, 12, 10, 9, 15), WhereQueryBuilder::IN, null)), 'WHERE id != ? AND score IN (?, ?, ?, ?, ?) ', 'WHERE id != ? ' . "\n" . 'AND score IN (?, ?, ?, ?, ?) ' . "\n", array(1, 8, 12, 10, 9, 15)), array(array(array('score', 5, WhereQueryBuilder::LESS_THAN_OR_EQUAL, null), array('score', 9, WhereQueryBuilder::GREATER_THAN_OR_EQUAL, WhereQueryBuilder::LOGICAL_OR)), 'WHERE score <= ? OR score >= ? ', 'WHERE score <= ? ' . "\n" . 'OR score >= ? ' . "\n", array(5, 9)), array(array(array('title', 'Dune', WhereQueryBuilder::NOT_EQUALS, null), array(WhereQueryBuilder::BRACKET_OPEN, WhereQueryBuilder::LOGICAL_OR), array('score', 5, WhereQueryBuilder::GREATER_THAN_OR_EQUAL, null), array('score', 10, WhereQueryBuilder::LESS_THAN_OR_EQUAL, WhereQueryBuilder::LOGICAL_AND), array(WhereQueryBuilder::BRACKET_CLOSE)), 'WHERE title != ? OR ( score >= ? AND score <= ? ) ', 'WHERE title != ? ' . "\n" . 'OR ' . "\n" . '( ' . "\n" . ' score >= ? ' . "\n" . ' AND score <= ? ' . "\n" . ') ' . "\n", array('Dune', 5, 10)), array(array(array('title', 'Dune', WhereQueryBuilder::NOT_EQUALS, null), array(WhereQueryBuilder::BRACKET_OPEN, WhereQueryBuilder::LOGICAL_OR), array('score', 5, WhereQueryBuilder::GREATER_THAN_OR_EQUAL, null), array('score', 10, WhereQueryBuilder::LESS_THAN_OR_EQUAL, WhereQueryBuilder::LOGICAL_AND), array(WhereQueryBuilder::BRACKET_OPEN, WhereQueryBuilder::LOGICAL_OR), array('published_at', '2011-10-02 00:00:00', WhereQueryBuilder::EQUALS, null), array(WhereQueryBuilder::BRACKET_CLOSE), array(WhereQueryBuilder::BRACKET_CLOSE)), 'WHERE title != ? OR ( score >= ? AND score <= ? OR ( published_at = ? ) ) ', 'WHERE title != ? ' . "\n" . 'OR ' . "\n" . '( ' . "\n" . ' score >= ? ' . "\n" . ' AND score <= ? ' . "\n" . ' OR ' . "\n" . ' ( ' . "\n" . ' published_at = ? ' . "\n" . ' ) ' . "\n" . ') ' . "\n", array('Dune', 5, 10, '2011-10-02 00:00:00')), array(array(array('title LIKE ?', '%the%', WhereQueryBuilder::RAW_CRITERIA, null)), 'WHERE title LIKE ? ', 'WHERE title LIKE ? ' . "\n", array('%the%')), array(array(array('score BETWEEN ? AND ? ', array(5, 8), WhereQueryBuilder::RAW_CRITERIA, null)), 'WHERE score BETWEEN ? AND ? ', 'WHERE score BETWEEN ? AND ? ' . "\n", array(5, 8)), array(array(array('title', '%Dune%', WhereQueryBuilder::NOT_LIKE, null), array('author_id', $subquery1, WhereQueryBuilder::SUB_QUERY_IN, null)), 'WHERE title NOT LIKE ? AND author_id IN ( SELECT id FROM author WHERE last_name LIKE ? ) ', 'WHERE title NOT LIKE ? ' . "\n" . 'AND author_id IN ' . "\n" . '( ' . "\n" . ' SELECT id FROM author WHERE last_name LIKE ? ' . "\n" . ') ' . "\n", array('%Dune%', '%Her%')), array(array(array('title', '%Dune%', WhereQueryBuilder::NOT_LIKE, null), array('author_id', $subquery1, WhereQueryBuilder::SUB_QUERY_NOT_IN, null)), 'WHERE title NOT LIKE ? AND author_id NOT IN ( SELECT id FROM author WHERE last_name LIKE ? ) ', 'WHERE title NOT LIKE ? ' . "\n" . 'AND author_id NOT IN ' . "\n" . '( ' . "\n" . ' SELECT id FROM author WHERE last_name LIKE ? ' . "\n" . ') ' . "\n", array('%Dune%', '%Her%')), array(array(array('title', '%Dune%', WhereQueryBuilder::NOT_LIKE, null), array('author_id', 'SELECT id FROM author WHERE last_name LIKE \'%Her%\'', WhereQueryBuilder::SUB_QUERY_NOT_IN, null)), 'WHERE title NOT LIKE ? AND author_id NOT IN ( SELECT id FROM author WHERE last_name LIKE \'%Her%\' ) ', 'WHERE title NOT LIKE ? ' . "\n" . 'AND author_id NOT IN ' . "\n" . '( ' . "\n" . ' SELECT id FROM author WHERE last_name LIKE \'%Her%\' ' . "\n" . ') ' . "\n", array('%Dune%')), array(array(array('title', '%Dune%', WhereQueryBuilder::NOT_LIKE, null), array('author_id', $subquery2, WhereQueryBuilder::SUB_QUERY_EXISTS, null)), 'WHERE title NOT LIKE ? AND EXISTS ( SELECT * FROM author WHERE first_name LIKE ? ) ', 'WHERE title NOT LIKE ? ' . "\n" . 'AND EXISTS ' . "\n" . '( ' . "\n" . ' SELECT * FROM author WHERE first_name LIKE ? ' . "\n" . ') ' . "\n", array('%Dune%', '%Ph%')), array(array(array('title', '%Dune%', WhereQueryBuilder::NOT_LIKE, null), array('', $subquery2, WhereQueryBuilder::SUB_QUERY_NOT_EXISTS, null)), 'WHERE title NOT LIKE ? AND NOT EXISTS ( SELECT * FROM author WHERE first_name LIKE ? ) ', 'WHERE title NOT LIKE ? ' . "\n" . 'AND NOT EXISTS ' . "\n" . '( ' . "\n" . ' SELECT * FROM author WHERE first_name LIKE ? ' . "\n" . ') ' . "\n", array('%Dune%', '%Ph%')), array(array(array('title', '%Dune%', WhereQueryBuilder::NOT_LIKE, null), array(WhereQueryBuilder::BRACKET_OPEN, WhereQueryBuilder::LOGICAL_OR), array('author_id', $subquery1, WhereQueryBuilder::SUB_QUERY_NOT_IN, null), array(WhereQueryBuilder::BRACKET_CLOSE)), 'WHERE title NOT LIKE ? OR ( author_id NOT IN ( SELECT id FROM author WHERE last_name LIKE ? ) ) ', 'WHERE title NOT LIKE ? ' . "\n" . 'OR ' . "\n" . '( ' . "\n" . ' author_id NOT IN ' . "\n" . ' ( ' . "\n" . ' SELECT id FROM author WHERE last_name LIKE ? ' . "\n" . ' ) ' . "\n" . ') ' . "\n", array('%Dune%', '%Her%')));
}
public function getQueryStringProvider()
{
$select1 = new SelectQueryBuilder();
$select1->select('AVG(price)');
$select1->from('OldBook', 'o');
return array(array('', array(), array(), array(), '', ''), array('book', array(array('score', null, 5), array('price', null, 8)), array(array('title', 'Dune', UpdateQueryBuilder::EQUALS, null)), array(5, 8, 'Dune'), 'UPDATE book SET score = ?, price = ? WHERE title = ? ', 'UPDATE book ' . "\n" . 'SET ' . "\n" . 'score = ?, ' . "\n" . 'price = ? ' . "\n" . 'WHERE title = ? ' . "\n"));
}
