Author: Kore Nordmann
Date: 2007-05-03 15:34:37 +0200 (Thu, 03 May 2007)
New Revision: 5022
Log:
- Implemented #8448 (Select Distinct not supported)
Modified:
trunk/Database/ChangeLog
trunk/Database/src/sqlabstraction/query_select.php
trunk/Database/tests/sqlabstraction/query_select_test.php
trunk/Database/tests/sqlabstraction/query_select_test_impl.php
trunk/Database/tests/sqlabstraction/query_subselect_test.php
trunk/Database/tests/sqlabstraction/query_subselect_test_impl.php
Modified: trunk/Database/ChangeLog
===================================================================
--- trunk/Database/ChangeLog 2007-05-03 13:24:47 UTC (rev 5021)
+++ trunk/Database/ChangeLog 2007-05-03 13:34:37 UTC (rev 5022)
@@ -9,8 +9,8 @@
ezcBaseInit.
- Fixed issue #9526: Implemented expression now() for each database handler.
- Fixed issue #10529: Tests for quoting of strings in query expressions.
+- Added feature #8448: Select Distinct not supported
-
1.2 - Monday 18 December 2006
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Modified: trunk/Database/src/sqlabstraction/query_select.php
===================================================================
--- trunk/Database/src/sqlabstraction/query_select.php 2007-05-03 13:24:47 UTC
(rev 5021)
+++ trunk/Database/src/sqlabstraction/query_select.php 2007-05-03 13:34:37 UTC
(rev 5022)
@@ -194,7 +194,8 @@
$cols = $this->getIdentifiers( $cols );
// glue string should be inserted each time but not before first entry
- if ( $this->selectString != 'SELECT ' )
+ if ( ( $this->selectString !== 'SELECT ' ) &&
+ ( $this->selectString !== 'SELECT DISTINCT ' ) )
{
$this->selectString .= ', ';
}
@@ -227,6 +228,65 @@
}
/**
+ * Opens the query and uses a distinct select on the columns you want to
+ * return with the query.
+ *
+ * selectDistinct() accepts an arbitrary number of parameters. Each
+ * parameter must contain either the name of a column or an array
+ * containing the names of the columns.
+ * Each call to selectDistinct() appends columns to the list of columns
+ * that will be used in the query.
+ *
+ * Example:
+ * <code>
+ * $q->selectDistinct( 'column1', 'column2' );
+ * </code>
+ * The same could also be written
+ * <code>
+ * $columns[] = 'column1';
+ * $columns[] = 'column2;
+ * $q->selectDistinct( $columns );
+ * </code>
+ * or using several calls
+ * <code>
+ * $q->selectDistinct( 'column1' )->select( 'column2' );
+ * </code>
+ *
+ * Each of above code produce SQL clause 'SELECT DISTINCT column1,
column2'
+ * for the query.
+ *
+ * You may call select() after calling selectDistinct() which will result
+ * in the additional columns beein added. A call of selectDistinct() after
+ * select() will result in an ezcQueryInvalidException.
+ *
+ * @throws ezcQueryVariableParameterException if called with no
parameters..
+ * @throws ezcQueryInvalidException if called after select()
+ * @param string|array(string) Either a string with a column name or an
array of column names.
+ * @return ezcQuery returns a pointer to $this.
+ */
+ public function selectDistinct()
+ {
+ if ( $this->selectString == null )
+ {
+ $this->selectString = 'SELECT DISTINCT ';
+ }
+ elseif ( strpos ( $this->selectString, 'DISTINCT' ) === false )
+ {
+ throw new ezcQueryInvalidException(
+ 'SELECT',
+ 'You can\'t use selectDistinct() after using select() in the
same query.'
+ );
+ }
+
+ // Call ezcQuerySelect::select() to do the parameter processing
+ $args = func_get_args();
+ return call_user_func_array(
+ array( $this, 'select' ),
+ $args
+ );
+ }
+
+ /**
* Select which tables you want to select from.
*
* from() accepts an arbitrary number of parameters. Each parameter
Modified: trunk/Database/tests/sqlabstraction/query_select_test.php
===================================================================
--- trunk/Database/tests/sqlabstraction/query_select_test.php 2007-05-03
13:24:47 UTC (rev 5021)
+++ trunk/Database/tests/sqlabstraction/query_select_test.php 2007-05-03
13:34:37 UTC (rev 5022)
@@ -567,6 +567,51 @@
$this->assertEquals( $reference, $this->q->getQuery() );
}
+ public function testBuildFromDistinctAndNormal()
+ {
+ $reference = 'SELECT DISTINCT foo, bar FROM table';
+ $this->q->selectDistinct( 'foo' )
+ ->select( 'bar' )
+ ->from( 'table' );
+
+ $this->assertEquals( $reference, $this->q->getQuery() );
+ }
+
+ public function testBuildFromNormalAndDistinct()
+ {
+ try
+ {
+ $this->q->select( 'foo' )
+ ->selectDistinct( 'bar' )
+ ->from( 'table' );
+ $this->fail( 'Expected ezcQueryInvalidException.' );
+ }
+ catch ( ezcQueryInvalidException $e )
+ {
+ return true;
+ }
+ }
+
+ public function testBuildFromDistinct()
+ {
+ $reference = 'SELECT DISTINCT * FROM table';
+ $this->q->selectDistinct( '*' )
+ ->from( 'table' );
+
+ $this->assertEquals( $reference, $this->q->getQuery() );
+ }
+
+ public function testBuildFromDistinctWhereOrderLimit()
+ {
+ $reference = 'SELECT DISTINCT * FROM table WHERE true ORDER BY name
LIMIT 1';
+ $this->q->selectDistinct( '*' )
+ ->from( 'table' )
+ ->where( 'true' )
+ ->orderBy( 'name' )
+ ->limit( 1 );
+ $this->assertEquals( $reference, $this->q->getQuery() );
+ }
+
public function testGetQueryInvalid()
{
try
Modified: trunk/Database/tests/sqlabstraction/query_select_test_impl.php
===================================================================
--- trunk/Database/tests/sqlabstraction/query_select_test_impl.php
2007-05-03 13:24:47 UTC (rev 5021)
+++ trunk/Database/tests/sqlabstraction/query_select_test_impl.php
2007-05-03 13:34:37 UTC (rev 5022)
@@ -282,6 +282,20 @@
$this->assertEquals( 1, $rows );
}
+ public function testBuildFromDistinct()
+ {
+ $this->q->selectDistinct( 'section' )
+ ->from( 'query_test' );
+
+ $stmt = $this->db->query( $this->q->getQuery() );
+ $rows = 0;
+ foreach ( $stmt as $row )
+ {
+ $rows++;
+ }
+ $this->assertEquals( 3, $rows );
+ }
+
// LOGIC TESTS
public function testSelectNone()
{
Modified: trunk/Database/tests/sqlabstraction/query_subselect_test.php
===================================================================
--- trunk/Database/tests/sqlabstraction/query_subselect_test.php
2007-05-03 13:24:47 UTC (rev 5021)
+++ trunk/Database/tests/sqlabstraction/query_subselect_test.php
2007-05-03 13:34:37 UTC (rev 5022)
@@ -94,6 +94,23 @@
}
+ public function testDistinctSubSelect()
+ {
+ $reference = 'SELECT DISTINCT * FROM table WHERE id = ( SELECT
DISTINCT column FROM table2 )';
+
+ $q2 = $this->q->subSelect();
+ $q2->selectDistinct( 'column' )->from( 'table2' );
+ $this->q
+ ->selectDistinct( '*' )
+ ->from( 'table' )
+ ->where(
+ $this->q->expr->eq( 'id', $q2 )
+ );
+
+ $this->assertEquals( $reference, $this->q->getQuery() );
+
+ }
+
public function testBindAuto()
{
$val1 = '';
Modified: trunk/Database/tests/sqlabstraction/query_subselect_test_impl.php
===================================================================
--- trunk/Database/tests/sqlabstraction/query_subselect_test_impl.php
2007-05-03 13:24:47 UTC (rev 5021)
+++ trunk/Database/tests/sqlabstraction/query_subselect_test_impl.php
2007-05-03 13:34:37 UTC (rev 5022)
@@ -58,6 +58,7 @@
$this->db->exec( "INSERT INTO query_test2 VALUES ( 2, 'IBM', 'Norway',
500 )" );
$this->db->exec( "INSERT INTO query_test2 VALUES ( 3, 'eZ systems',
'Ukraine', 10 )" );
$this->db->exec( "INSERT INTO query_test2 VALUES ( 4, 'IBM',
'Germany', null )" );
+ $this->db->exec( "INSERT INTO query_test2 VALUES ( 5, 'Intel', 'USA',
5000 )" );
}
protected function tearDown()
@@ -95,6 +96,35 @@
$this->assertEquals( 'Germany', $result[1]['section'] );
}
+ public function testInnerDistinctSubSelectBindParam()
+ {
+ $name = 'IBM';
+ $name2 = 'company';
+ $q = new ezcQuerySelect( ezcDbInstance::get() );
+
+ // subselect
+ $q2 = $q->subSelect();
+ $q->expr->setValuesQuoting( false );
+
+ // bind values
+ $q2->selectDistinct( 'section' )
+ ->from( 'query_test' );
+
+ $q->selectDistinct( 'company' )
+ ->from( 'query_test2' )
+ ->where( $q->expr->in( 'section', $q2 ) );
+
+ $stmt = $q->prepare();
+ $stmt->execute();
+
+ $result = $stmt->fetchAll();
+
+ $this->assertEquals( 'eZ systems', $result[0]['company'] );
+ $this->assertEquals( 'IBM', $result[1]['company'] );
+
+ $this->assertSame( 2, count( $result ) );
+ }
+
public function testSubSelectBindParam()
{
$name = 'IBM';
--
svn-components mailing list
[email protected]
http://lists.ez.no/mailman/listinfo/svn-components
