leftjoin converter to rightjoin why?

Posts   
 
    
OSSistemes
User
Posts: 19
Joined: 20-Nov-2019
# Posted on: 09-Jan-2023 20:18:56   

Please somebody can explain me why this

DynamicQuery<Entities.Manual.LstArticle.ArticleCostStockEntity> q = qf.Create()
                        .Select(() => new Entities.Manual.LstArticle.ArticleCostStockEntity
                        {
                            Codi = ArticleFields.Codi.ToValue<string>(),
                            Descripcio = ArticleFields.Descripcio.ToValue<string>(),
                            Preu = ArticleTarifaFields.Preu.ToValue<decimal>(),
                            Stock = ArticleStockFields.Stock.ToValue<decimal>(),
                        })
                        .From(qf.Article
                        .LeftJoin(ArticleTarifaEntity.Relations.ArticleEntityUsingArticleCodi)
                        .LeftJoin(ArticleStockEntity.Relations.ArticleEntityUsingArticleCodi))
                        .Where(ArticleTarifaFields.TarifaCodi == tarifacodi).AndWhere(ArticleStockFields.BotigaCodi.IsNull());

is converter to this in MYSQL adapter

SELECT `article`.`codi` AS `Codi`, `article`.`descripcio` AS `Descripcio`, `article_tarifa`.`preu` AS `Preu`, `article_stock`.`stock` AS `Stock`
FROM ((`article`
RIGHT JOIN `article_tarifa` ON `article`.`codi`=`article_tarifa`.`article_codi`)
RIGHT JOIN `article_stock` ON `article`.`codi`=`article_stock`.`article_codi`)
WHERE ((`article_tarifa`.`tarifa_codi` = 1 AND `article_stock`.`botiga_codi` IS NULL))

Why change leftjoin to rightjoin

Thanks team

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 10-Jan-2023 09:35:21   

In SQL, after the first join, the joins are specified using only <join operator> <table> constructs. This means that if you do:

A left join B C left join B

The runtime isn't able to specify this as-is, it has to specify the join with C with the construct <join operator> <table>. So it will become:

A left join B right join C

Our relationship definitions in the API allow you to specify the relationship in full, so C left join B, so you don't have to worry about whether which side is already in the join list (in the example above, in the case of C left join B, 'B' is already in the join list). So it flips it around if it has to, as in the example above.

In your query, .LeftJoin(ArticleStockEntity.Relations.ArticleEntityUsingArticleCodi)) means you join towards ArticleStock, so ArticleStock left join Article, but article is already in the join list, so it will become <joins already there> right join ArticleStock, which is the same thing.

Frans Bouma | Lead developer LLBLGen Pro