Open AnrDaemon opened 4 months ago
You can achieve this with workaround below ^_^
$select->from(
new Fragment('('.$selectSub.') AS table1'),
)
@roxblnfk may be we need to add some extra class for this case e.g. 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();
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` > ?;
@gam6itko yes it is also my main idea about it
See https://github.com/cycle/database/issues/139#issuecomment-1783856089
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:
The generated query looks like
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.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