It is often requested to add search conditions to a selection query that has already been created.
We would like to use Carbunql in such situations, but the GetInternalQueries function is not very user-friendly.
Direction
We want to be able to efficiently search and modify the query sources (tables, views, and CTEs) that make up a query.
In addition, since it is better for performance to have the query to be modified as close to the root as possible, it would be good to be able to manage additional information that makes it easier to identify the target of modification.
Solution
We want to allow a query source to have information such as sequence, branch, level, column, and the selection query it belongs to.
The sequence indicates a sequential number within the selection query it belongs to, and is unique within the selection query it belongs to. If it moves to another query source (such as a subquery), a new number is assigned starting from 1.
The branch is a branch number that indicates the tree structure of the entire query. A different branch number is assigned when a table is joined. In the case of a subquery or CTE, the branch number of the caller is inherited by the query source in the From clause. When there is an outer join, if the search condition filter is not applied for each branch number, SQL statements with performance problems may be generated, so this is useful for preventing this.
The level indicates the depth of the query source. The initial value is 1, and it is incremented each time it passes through a subquery or CTE. The larger the number, the deeper (the deeper the root). It is preferable to filter before processing and aggregation, so applying an appropriate amount of filtering to high-level items has a high performance effect.
In summary, most cases can be covered by applying a filter to the select query that the highest-level query source belongs to for each branch.
Also, if multiple query sources are detected as filter targets, using a sequence makes it easier to narrow them down uniquely.
Background
It is often requested to add search conditions to a selection query that has already been created.
We would like to use Carbunql in such situations, but the GetInternalQueries function is not very user-friendly.
Direction
We want to be able to efficiently search and modify the query sources (tables, views, and CTEs) that make up a query.
In addition, since it is better for performance to have the query to be modified as close to the root as possible, it would be good to be able to manage additional information that makes it easier to identify the target of modification.
Solution
We want to allow a query source to have information such as sequence, branch, level, column, and the selection query it belongs to.
The sequence indicates a sequential number within the selection query it belongs to, and is unique within the selection query it belongs to. If it moves to another query source (such as a subquery), a new number is assigned starting from 1.
The branch is a branch number that indicates the tree structure of the entire query. A different branch number is assigned when a table is joined. In the case of a subquery or CTE, the branch number of the caller is inherited by the query source in the From clause. When there is an outer join, if the search condition filter is not applied for each branch number, SQL statements with performance problems may be generated, so this is useful for preventing this.
The level indicates the depth of the query source. The initial value is 1, and it is incremented each time it passes through a subquery or CTE. The larger the number, the deeper (the deeper the root). It is preferable to filter before processing and aggregation, so applying an appropriate amount of filtering to high-level items has a high performance effect.
In summary, most cases can be covered by applying a filter to the select query that the highest-level query source belongs to for each branch.
Also, if multiple query sources are detected as filter targets, using a sequence makes it easier to narrow them down uniquely.
Reference issue
https://github.com/mk3008/Carbunql/issues/158