[Feature]: Cycle\Database\Query\SelectQuery::from missing "alias" parameter

[AnrDaemon]

Is your feature request related to a problem? Please describe.

When writing complex requests, especially with subqueries, Cycle generates unusable or outright wrong SQL queries.

Example:

<?php

        $arr = $database->select([
            "id",
        ])->from(
            $this->database()->select([
                "id",
                new Fragment("COUNT(*) OVER (PARTITION BY field1, field2) AS d"),
            ])->from('a_table')
        )
            ->where("field3", "=", new Parameter(1))
            ->where("d", ">", new Parameter(1))
            ->fetchAll();

The generated query looks like

SELECT `id`
FROM (SELECT `id`, COUNT(*) OVER (PARTITION BY tag, component) AS d
FROM `a_table`)
WHERE `field3` = ? AND `d` > ?;

which looks fine, but in fact is not correct MySQL.

Describe the solution you'd like

Simply adding subquery table alias … FROM (SELECT …) AS t WHERE … fixes that in an instant.

<?php

        $arr = $database->select([
            "id",
        ])->from(
            $this->database()->select([
                "id",
                new Fragment("COUNT(*) OVER (PARTITION BY field1, field2) AS d"),
            ])->from('a_table'),
            "t"
        )
            ->where("field3", "=", new Parameter(1))
            ->where("d", ">", new Parameter(1))
            ->fetchAll();

SELECT `id`
FROM (SELECT `id`, COUNT(*) OVER (PARTITION BY tag, component) AS d
FROM `a_table`) AS `t`
WHERE `field3` = ? AND `d` > ?;

Describe alternatives you've considered

Using ->innerJoin() method with appropriate ->on() bindings works around the deficiency, but defy the code readability.

Additional context

No response

[gam6itko]

You can achieve this with workaround below ^_^

$select->from(
    new Fragment('('.$selectSub.') AS table1'),
)

[gam6itko]

@roxblnfk may be we need to add some extra class for this case e.g. DerivedTable

DerivedTable

$subQuery = $this
    ->database()
    ->select([
        "id",
        new Fragment("COUNT(*) OVER (PARTITION BY field1, field2) AS d"),
    ])
    ->from('a_table');

$derivedTable = new DerivedTable(
    query: subQuery,
    alias: 't'
);

$arr = $database
    ->select(["id"])
    ->from( $derivedTable )
    ->where("field3", "=", new Parameter(1))
    ->where("d", ">", new Parameter(1))
    ->fetchAll();

CTETable

Also we can add a new feature CTETable which render sql query with with

$subQuery = $this
    ->database()
    ->select([
        "id",
        new Fragment("COUNT(*) OVER (PARTITION BY field1, field2) AS d"),
    ])
    ->from('a_table');

$cteTable = new CTETable(
    query: subQuery,
    alias: 't'
);

$arr = $database
    ->select(["id"])
    ->from( $cteTable )
    ->where("field3", "=", new Parameter(1))
    ->where("d", ">", new Parameter(1))
    ->fetchAll();

WITH t AS
(
  SELECT]
    `id`, 
    COUNT(*) OVER (PARTITION BY tag, component) AS d 
  FROM `a_table`
)
SELECT `id` FROM `t` WHERE `field3` = ? AND `d` > ?;

[roxblnfk]

@gam6itko yes it is also my main idea about it

See https://github.com/cycle/database/issues/139#issuecomment-1783856089