Add ConnectionInterface::select() method (#984)

Tigrov · web-flow · commit bfd18e2a9808 · 2025-06-12T13:00:45.000+07:00
diff --git a/CHANGELOG.md b/CHANGELOG.md
@@ -95,6 +95,7 @@
 - Chg #980: Add constructor with DB connection to `AbstractCommand` (@vjik)
 - Enh #979: Allow `ExpressionInterface` for column definitions when create table (@Tigrov)
 - Enh #982: Reduce binding parameters (@Tigrov)
+- New #984: Add `createQuery()` and `select()` methods to `ConnectionInterface` (@Tigrov)
 - Chg #985: Rename `insertWithReturningPks()` to `insertReturningPks()` in `CommandInterface` and `DMLQueryBuilderInterface` (@Tigrov)
 
 ## 1.3.0 March 21, 2024
diff --git a/UPGRADE.md b/UPGRADE.md
@@ -130,6 +130,8 @@ Each table column has its own class in the `Yiisoft\Db\Schema\Column` namespace
 - `QueryPartsInterface::setHaving()` - overwrites the `HAVING` part of the query;
 - `ConnectionInterface::getColumnFactory()` - returns the column factory object for concrete DBMS;
 - `ConnectionInterface::getServerInfo()` - returns `ServerInfoInterface` instance which provides server information;
+- `ConnectionInterface::createQuery()` - creates a `Query` object;
+- `ConnectionInterface::select()` - creates a `Query` object with the specified columns to be selected;
 - `QueryBuilderInterface::buildColumnDefinition()` - builds column definition for `CREATE TABLE` statement;
 - `QueryBuilderInterface::buildValue()` - converts a value to its SQL representation with binding parameters if necessary;
 - `QueryBuilderInterface::prepareParam()` - converts a `ParamInterface` object to its SQL representation;
diff --git a/src/Connection/AbstractConnection.php b/src/Connection/AbstractConnection.php
@@ -6,8 +6,10 @@
 
 use Closure;
 use Throwable;
+use Yiisoft\Db\Expression\ExpressionInterface;
 use Yiisoft\Db\Query\BatchQueryResult;
 use Yiisoft\Db\Query\BatchQueryResultInterface;
+use Yiisoft\Db\Query\Query;
 use Yiisoft\Db\Query\QueryInterface;
 use Yiisoft\Db\Schema\TableSchemaInterface;
 use Yiisoft\Db\Transaction\TransactionInterface;
@@ -43,6 +45,11 @@ public function createBatchQueryResult(QueryInterface $query): BatchQueryResultI
         return new BatchQueryResult($query);
     }
 
+    public function createQuery(): QueryInterface
+    {
+        return new Query($this);
+    }
+
     public function getTablePrefix(): string
     {
         return $this->tablePrefix;
@@ -68,6 +75,13 @@ public function setEnableSavepoint(bool $value): void
         $this->enableSavepoint = $value;
     }
 
+    public function select(
+        array|bool|float|int|string|ExpressionInterface $columns = [],
+        ?string $option = null,
+    ): QueryInterface {
+        return $this->createQuery()->select($columns, $option);
+    }
+
     public function setTablePrefix(string $value): void
     {
         $this->tablePrefix = $value;
diff --git a/src/Connection/ConnectionInterface.php b/src/Connection/ConnectionInterface.php
@@ -11,8 +11,10 @@
 use Yiisoft\Db\Exception\InvalidCallException;
 use Yiisoft\Db\Exception\InvalidConfigException;
 use Yiisoft\Db\Exception\NotSupportedException;
+use Yiisoft\Db\Expression\ExpressionInterface;
 use Yiisoft\Db\Query\BatchQueryResultInterface;
 use Yiisoft\Db\Query\QueryInterface;
+use Yiisoft\Db\Query\QueryPartsInterface;
 use Yiisoft\Db\QueryBuilder\QueryBuilderInterface;
 use Yiisoft\Db\Schema\Column\ColumnFactoryInterface;
 use Yiisoft\Db\Schema\QuoterInterface;
@@ -28,6 +30,7 @@
  * different database systems without having to worry about the specific details of each one.
  *
  * @psalm-type ParamsType = array<non-empty-string, mixed>|list<mixed>
+ * @psalm-import-type SelectValue from QueryPartsInterface
  */
 interface ConnectionInterface
 {
@@ -71,6 +74,13 @@ public function createBatchQueryResult(QueryInterface $query): BatchQueryResultI
      */
     public function createCommand(?string $sql = null, array $params = []): CommandInterface;
 
+    /**
+     * Creates a query instance.
+     *
+     * @return QueryInterface The query instance.
+     */
+    public function createQuery(): QueryInterface;
+
     /**
      * Create a transaction instance.
      *
@@ -203,6 +213,30 @@ public function quoteValue(mixed $value): mixed;
      */
     public function setEnableSavepoint(bool $value): void;
 
+    /**
+     * Creates a new {@see Query} instance with the specified columns to be selected.
+     *
+     * @param array|ExpressionInterface|scalar $columns The columns to be selected.
+     * Columns can be specified in either a string (for example `id, name`) or an array (such as `['id', 'name']`).
+     * Columns can be prefixed with table names (such as `user.id`) and/or contain column aliases
+     * (for example `user.id AS user_id`).
+     * The method will automatically quote the column names unless a column has some parenthesis (which means the
+     * column has a DB expression).
+     * A DB expression may also be passed in form of an {@see ExpressionInterface} object.
+     * Note that if you are selecting an expression like `CONCAT(first_name, ' ', last_name)`, you should use an array
+     * to specify the columns. Otherwise, the expression may be incorrectly split into several parts.
+     * When the columns are specified as an array, you may also use array keys as the column aliases (if a column
+     * doesn't need alias, don't use a string key).
+     * @param string|null $option More option that should be appended to the 'SELECT' keyword. For example, in MySQL,
+     * the option 'SQL_CALC_FOUND_ROWS' can be used.
+     *
+     * @psalm-param SelectValue|scalar|ExpressionInterface $columns
+     */
+    public function select(
+        array|bool|float|int|string|ExpressionInterface $columns = [],
+        ?string $option = null,
+    ): QueryInterface;
+
     /**
      * The common prefix or suffix for table names.
      * If a table name is `{{%TableName}}`, then the percentage
diff --git a/src/Debug/ConnectionInterfaceProxy.php b/src/Debug/ConnectionInterfaceProxy.php
@@ -8,6 +8,7 @@
 use Yiisoft\Db\Command\CommandInterface;
 use Yiisoft\Db\Connection\ConnectionInterface;
 use Yiisoft\Db\Connection\ServerInfoInterface;
+use Yiisoft\Db\Expression\ExpressionInterface;
 use Yiisoft\Db\Query\BatchQueryResultInterface;
 use Yiisoft\Db\Query\QueryInterface;
 use Yiisoft\Db\QueryBuilder\QueryBuilderInterface;
@@ -51,6 +52,11 @@ public function createCommand(?string $sql = null, array $params = []): CommandI
         );
     }
 
+    public function createQuery(): QueryInterface
+    {
+        return $this->connection->createQuery();
+    }
+
     public function createTransaction(): TransactionInterface
     {
         return new TransactionInterfaceDecorator(
@@ -141,6 +147,13 @@ public function setEnableSavepoint(bool $value): void
         $this->connection->setEnableSavepoint($value);
     }
 
+    public function select(
+        array|bool|float|int|string|ExpressionInterface $columns = [],
+        ?string $option = null,
+    ): QueryInterface {
+        return $this->connection->select($columns, $option);
+    }
+
     public function setTablePrefix(string $value): void
     {
         $this->connection->setTablePrefix($value);
diff --git a/tests/Db/Connection/ConnectionTest.php b/tests/Db/Connection/ConnectionTest.php
@@ -4,8 +4,13 @@
 
 namespace Yiisoft\Db\Tests\Db\Connection;
 
+use PHPUnit\Framework\Attributes\TestWith;
 use Yiisoft\Db\Exception\NotSupportedException;
+use Yiisoft\Db\Expression\Expression;
+use Yiisoft\Db\Expression\ExpressionInterface;
+use Yiisoft\Db\Query\Query;
 use Yiisoft\Db\Tests\AbstractConnectionTest;
+use Yiisoft\Db\Tests\Support\Assert;
 use Yiisoft\Db\Tests\Support\DbHelper;
 use Yiisoft\Db\Tests\Support\Stub\ColumnFactory;
 use Yiisoft\Db\Tests\Support\Stub\Connection;
@@ -45,4 +50,32 @@ public function testConstructColumnFactory(): void
 
         $this->assertSame($columnFactory, $db->getColumnFactory());
     }
+
+    public function testCreateQuery(): void
+    {
+        $db = $this->getConnection();
+
+        $this->assertInstanceOf(Query::class, $db->createQuery());
+    }
+
+    #[TestWith(['columns' => 'column1'])]
+    #[TestWith(['columns' => 'now()'])]
+    #[TestWith(['columns' => true])]
+    #[TestWith(['columns' => 1])]
+    #[TestWith(['columns' => 1.2])]
+    #[TestWith(['columns' => new Expression('now()')])]
+    #[TestWith(['columns' => ['column1', 'now()', new Expression('now()')]])]
+    public function testSelect(array|bool|float|int|string|ExpressionInterface $columns, ?string $option = null): void
+    {
+        $db = $this->getConnection();
+
+        Assert::objectsEquals($db->select($columns, $option), $db->createQuery()->select($columns, $option));
+    }
+
+    public function testSelectWithoutParams(): void
+    {
+        $db = $this->getConnection();
+
+        Assert::objectsEquals($db->select(), $db->createQuery());
+    }
 }
