public void TestJoin() { Select sel = Qb.Select("a").From("lt").Join("rt", JoinCond.Fields("id_0")); From lt = From.Table("lt"); From rt = From.Table("rt"); Logic l = Logic.And(Cond.Equal(Expr.Field("id_0", lt), Expr.Field("id_0", rt))); sel = Qb.Select("a").From("lt").Join(JoinType.Left, lt, rt, l); }
public void TestJoinCond() { JoinCond j = JoinCond.Fields("a"); }
public void TestWhere() { Select sel = Qb.Select("*") .From("tab") .Where( Cond.Equal("a", 1), Cond.Greater("b", 2) ); Renderer.ISqlOmRenderer renderer = new Renderer.SqlServerRenderer(); string sql = renderer.RenderSelect(sel); Assert.Equal("select * from [tab] where (([a] = 1 and [b] > 2))", sql); sel = Qb.Select("*") .From("tab") .Where( Cond.NotIn(Expr.Field("a"), 1, 2), Cond.NotIn("a", 1, 2), Cond.NotIn("b", "bb", "bbb"), Cond.NotIn("a", DateTime.Now, DateTime.UtcNow) ); sql = renderer.RenderSelect(sel); sel = Qb.Select("*") .From("tab") .Where(Logic.Or( Cond.Equal("a", 1), Cond.Equal("a", 2) )); sql = renderer.RenderSelect(sel); sel = Qb.Select("*") .From("tab") .Where( Logic.And( Cond.Equal("a", 1), Cond.Greater("b", 2) ) ); sql = renderer.RenderSelect(sel); Assert.Equal("select * from [tab] where (([a] = 1 and [b] > 2))", sql); sel = Qb.Select("*") .From("tab") .Where( Logic.Or( Cond.Equal("a", 1), Cond.Greater("b", 2) ) ); sql = renderer.RenderSelect(sel); Assert.Equal("select * from [tab] where (([a] = 1 or [b] > 2))", sql); From customer = From.Table("Customers", "c"); From orders = From.Table("Orders", "o"); Select inner = Qb.Select( Column.New("FirstName", customer), Column.New("LastName", customer), Column.New("Count", "sum", orders, AggFunc.Sum) ) .From(customer) .Join(JoinType.Left, customer, orders, JoinCond.Fields("Id", "CustomerId")) .GroupBy("FirstName", customer) .GroupBy("LastName", customer); From t = From.SubQuery(inner, "t"); sel = Qb.Select( Column.New("FirstName", t), Column.New("LastName", t), Column.New(Expr.IfNull(Expr.Field("sum", t), 0), "total") ) .From(From.SubQuery(inner, "t")) .Where(Cond.NotLike(Expr.Field("FirstName"), Expr.String("aa$"))); sql = renderer.RenderSelect(sel); Renderer.PostgreSqlRenderer pg = new Renderer.PostgreSqlRenderer(); sql = pg.RenderSelect(sel); List <string> grants = new List <string>() { "1.$", "2.$" }; List <string> bans = new List <string>() { "3.$", "4.$" }; string ss = GetSqlDemans(grants, bans); sel = Qb.Select("*").From("tab").Where(Cond.Like("FirstName", "%abc%")); sql = renderer.RenderSelect(sel); renderer = new Renderer.PostgreSqlRenderer(); sql = renderer.RenderSelect(sel); }