/**
* Extend SQL query with segment expressions
*
* @param string select clause
* @param array of table names (without prefix)
* @param string where clause
* @param string (optional) order by clause
* @param string (optional) group by clause
* @return string entire select query
*/
public function getSelectQuery($select, $from, $where = false, $bind = array(), $orderBy = false, $groupBy = false)
{
$joinWithSubSelect = false;
if (!is_array($from)) {
$from = array($from);
}
if (!$this->isEmpty()) {
$this->segment->parseSubExpressionsIntoSqlExpressions($from);
$joins = $this->generateJoins($from);
$from = $joins['sql'];
$joinWithSubSelect = $joins['joinWithSubSelect'];
$segmentSql = $this->segment->getSql();
$segmentWhere = $segmentSql['where'];
if (!empty($segmentWhere)) {
if (!empty($where)) {
$where = "( {$where} )\n\t\t\t\tAND\n\t\t\t\t({$segmentWhere})";
} else {
$where = $segmentWhere;
}
}
$bind = array_merge($bind, $segmentSql['bind']);
} else {
$joins = $this->generateJoins($from);
$from = $joins['sql'];
$joinWithSubSelect = $joins['joinWithSubSelect'];
}
if ($joinWithSubSelect) {
$sql = $this->buildWrappedSelectQuery($select, $from, $where, $orderBy, $groupBy);
} else {
$sql = $this->buildSelectQuery($select, $from, $where, $orderBy, $groupBy);
}
$return = array('sql' => $sql, 'bind' => $bind);
//var_dump($return);
return $return;
}
public function getSql()
{
if ($this->isEmpty()) {
return array('sql' => '', 'bind' => array());
}
$this->segment->parseSubExpressionsIntoSqlExpressions();
return $this->segment->getSql();
}
/**
* @dataProvider getOperationSegmentExpressions
* @group Core
* @group SegmentExpression
*/
public function testSegmentSqlWithOperations($expression, $expectedSql)
{
$segment = new Piwik_SegmentExpression($expression);
$segment->parseSubExpressions();
$segment->parseSubExpressionsIntoSqlExpressions();
$processed = $segment->getSql();
$expectedSql['join'] = '';
$this->assertEquals($expectedSql, $processed);
}
public function test_SegmentSql_withOperations()
{
// Filter expression => SQL string + Bind values
$expressionToSql = array('A==B%' => array('where' => " A = ? ", 'bind' => array('B%')), 'ABCDEF====B===' => array('where' => " ABCDEF = ? ", 'bind' => array('==B===')), 'A===B;CDEF!=C!=' => array('where' => " A = ? AND CDEF <> ? ", 'bind' => array('=B', 'C!=')), 'A==B,C==D' => array('where' => " (A = ? OR C = ? )", 'bind' => array('B', 'D')), 'A!=B;C==D' => array('where' => " A <> ? AND C = ? ", 'bind' => array('B', 'D')), 'A!=B;C==D,E!=Hello World!=' => array('where' => " A <> ? AND (C = ? OR E <> ? )", 'bind' => array('B', 'D', 'Hello World!=')), 'A>B' => array('where' => " A > ? ", 'bind' => array('B')), 'A<B' => array('where' => " A < ? ", 'bind' => array('B')), 'A<=B' => array('where' => " A <= ? ", 'bind' => array('B')), 'A>=B' => array('where' => " A >= ? ", 'bind' => array('B')), 'ABCDEF>=>=>=B===' => array('where' => " ABCDEF >= ? ", 'bind' => array('>=>=B===')), 'A>=<=B;CDEF>G;H>=I;J<K;L<=M' => array('where' => " A >= ? AND CDEF > ? AND H >= ? AND J < ? AND L <= ? ", 'bind' => array('<=B', 'G', 'I', 'K', 'M')), 'A>=B;C>=D,E<w_ow great!' => array('where' => " A >= ? AND (C >= ? OR E < ? )", 'bind' => array('B', 'D', 'w_ow great!')), 'A=@B_' => array('where' => " A LIKE ? ", 'bind' => array('%B\\_%')), 'A!@B%' => array('where' => " A NOT LIKE ? ", 'bind' => array('%B\\%%')));
foreach ($expressionToSql as $expression => $expectedSql) {
$segment = new Piwik_SegmentExpression($expression);
$segment->parseSubExpressions();
$segment->parseSubExpressionsIntoSqlExpressions();
$processed = $segment->getSql();
$expectedSql['join'] = '';
$out = '<br/>' . var_export($processed, true) . "\n *DIFFERENT FROM* " . var_export($expectedSql, true);
$this->assertEqual($processed, $expectedSql, str_replace('%', '%%', $out));
}
}
public function getSql( $fieldsAvailableInTable = array(), $joinedTableName = false)
{
if($this->isEmpty())
{
return array('sql' => '', 'bind' => array(), 'sql_join_visits' => '');
}
$this->segment->parseSubExpressionsIntoSqlExpressions($fieldsAvailableInTable, $joinedTableName);
$return = $this->segment->getSql();
$return['sql_join_visits'] = '';
if(!empty($fieldsAvailableInTable))
{
if(!$this->isSegmentAvailable($fieldsAvailableInTable))
{
$return['sql_join_visits'] = "LEFT JOIN ".Piwik_Common::prefixTable('log_visit')." AS log_visit USING(idvisit)";
}
}
return $return;
}