Comments (9)
Hi thanks for your feedback, actually all CTEs should be attached to the main query and not to any other CTE or subquery.
so you should rewrite your programs as the following:
var cte1 = new Query("Countries").Where("Population", ">", 100);
var cte2 = new Query("Cities").Join("Cte1", j => j.On("Cte1.Id", "Cities.CountryId"));
var main = new Query("Users").With("Cte1", cte1).With("Cte2", cte2).From("Cte2")
let me know if this work.
from querybuilder.
Yeah that method does work. With the way I've structured my solution, each query only has access to things they are directly related to. CTE2 has a reference to CTE1. Main only has a reference to CTE2.
Would you take a pull request for having the CTEs pulled from sub queries up to the main query during compilation?
from querybuilder.
I think pulling the CTEs to the top level automatically may lead to confusion in certain cases, for example when a CTE is referenced by more than one query, this may lead to adding the same CTE more than once in the main query.
one example would be:
var cte1 = new Query("table1");
var cte2 = new Query("table2").Join("Cte1", "Cte1.Col", "table2.col");
var cte3 = new Query("table3").Join("Cte1", "Cte1.Col", "table3.col");
var main = new Query().With("Cte2", cte2).With("Cte3", cte3); // adds cte1 twice
Maybe leaving this to the developer is more appropriate, anyway we can keep the discussion open and get more feedback.
for the moment we can enhance the usage examples in the docs, if you find so please open an issue there.
from querybuilder.
I understand where you're coming from. However, the current behaviour has already led to developer confusion because I added a CTE in the query where the CTE is actually referenced and this wasn't rendered in the subsequent SQL.
Maybe this needs to throw an exception when SqlKata detects a nested CTE? In terms of the example above, that's not quite what I had in mind and I agree that example would lead to developer confusion.
Maybe it was just an omission but cte1 was not added to cte2 & cte3 using the With syntax.
What this might look like:
var cte1 = new Query("table1");
var cte2 = new Query("table2").With("Cte1", cte1).Join("Cte1", "Cte1.Col", "table2.col");
var cte3 = new Query("table3").With("Cte1", cte1).Join("Cte1", "Cte1.Col", "table3.col");
var main = new Query().With("Cte2", cte2).With("Cte3", cte3);
To avoid the duplication aspects, Cte1 would be added once due to an Object reference equality check.
As you say, maybe it is more appropriate to leave it to the developer, the current behaviour allows SqlKata to produce unexpected and broken SQL.
Anyway, just a suggestion, thanks for taking the time to reply.
from querybuilder.
@SaltyDH, I will reconsider this, I am convinced with your approach more honestly, do you have a PR for this feature ?
from querybuilder.
@BoostIO funded this issue with $20. Visit this issue on Issuehunt
from querybuilder.
@edokan has started working. Visit this issue on Issuehunt
from querybuilder.
@edokan has submitted output. Visit this issue on Issuehunt
from querybuilder.
@ahmad-moussawi has rewarded. Visit this issue on Issuehunt
from querybuilder.
Related Issues (20)
- RobiniaDocs API Explorer
- Compile query with parameters for ODBC Connection
- Assigning NULL value to column with AsUpdate throws NotSupportedException HOT 1
- db access where day error No value given for one or more required parameters.
- [SQLServer] insert bool value #437 HOT 1
- Possible documentation error
- Distinct AsCount error
- Vulnerability in System.Text.RegularExpressions 4.3.0
- FromRaw can't translate list type parameter properly HOT 1
- how to use include HOT 2
- Question: Combining multiple Statements HOT 1
- Error on WhereContains HOT 1
- Getting Raw SQL from the package, without passing it to the database engine HOT 5
- Error when trying to insert into Always Encrypted column
- SqlServerCompiler Limit generates different query on compile compared to previous versions HOT 3
- Postgres over-escaping ? HOT 1
- `With` missing if defined in sub query HOT 2
- Use RetryProvider in SqlKata.Execution
- [Ignore] atbt discerning Insert/Update
- Next release, 3.0.0 from 2022 year is still beta.
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from querybuilder.