Nested CTE is not rendered in top level query about querybuilder HOT 9 CLOSED

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.