From 5b2c9d4ca012f506cabe82150dae5c083f8a8096 Mon Sep 17 00:00:00 2001 From: Hafiz Shafruddin Date: Tue, 3 May 2022 23:15:19 +1000 Subject: [PATCH 01/10] Add docs on using ExprP() for custom WHERE sql statement --- doc/md/predicates.md | 36 ++++++++++++++++++++++++++++++++++++ 1 file changed, 36 insertions(+) diff --git a/doc/md/predicates.md b/doc/md/predicates.md index 3fafbcd0e9..cc23354e4a 100755 --- a/doc/md/predicates.md +++ b/doc/md/predicates.md @@ -205,6 +205,42 @@ The above code will produce the following SQL query: SELECT DISTINCT `pets`.`id`, `pets`.`owner_id`, `pets`.`name`, `pets`.`age`, `pets`.`species` FROM `pets` WHERE `name` LIKE '_B%' ``` +#### Escape hatch for `WHERE` + +Sometimes you want to use builtin sql functions such as a `DATE()` function to a `WHERE` statement. +It can be achieved by using `ExprP()`. + +```go +users := client.User.Query(). + Where(func(s *ent.Selector) { + s.Where(sql.ExprP("DATE(last_login_at >= ?", dateVar)) + }). + AllX(ctx) +``` + +The above code will produce the following SQL query: + +```sql +SELECT `users`.`id`, `users`.`last_login_at` FROM users WHERE DATE(last_login_at) <= ?; +``` + +You may also perform a more complex sql query, for example `DATE_ADD()` + +```go +events := r.ent.Event.Query(). + Where(func(s *entsql.Selector) { + s.Where(entsql.ExprP("DATE_ADD(date, INTERVAL duration MINUTE) BETWEEN ? AND ?", startDateVar, endDateVar)) + }). + AllX(ctx) +``` + +The above code will produce the following SQL query: + +```sql +SELECT `events`.`id`, `events`.`date`, `events`.`duration` +FROM events WHERE DATE_ADD(date, INTERVAL duration MINUTE) BETWEEN ? AND ?; +``` + ## JSON predicates JSON predicates are not generated by default as part of the code generation. However, ent provides an official package From 12a3e5cde23b9238533a28de4c895108f2d7398b Mon Sep 17 00:00:00 2001 From: Hafiz Shafruddin Date: Thu, 5 May 2022 16:19:24 +1000 Subject: [PATCH 02/10] Add ExprP() examples using integration test --- dialect/sql/builder_test.go | 10 ++++++++++ 1 file changed, 10 insertions(+) diff --git a/dialect/sql/builder_test.go b/dialect/sql/builder_test.go index 9435aa8812..9a36ff7a33 100644 --- a/dialect/sql/builder_test.go +++ b/dialect/sql/builder_test.go @@ -1472,6 +1472,16 @@ func TestBuilder(t *testing.T) { wantQuery: `SELECT * FROM "test" WHERE nlevel("path") > $1`, wantArgs: []interface{}{1}, }, + { + input: Select("id").From(Table("users")).Where(ExprP("Date(last_login_at) >= ?")), + wantQuery: "SELECT `id` FROM `users` WHERE Date(last_login_at) >= ?", + wantArgs: []interface{}{"2022-05-03"}, + }, + { + input: Select("id").From(Table("events")).Where(ExprP("DATE_ADD(date, INTERVAL duration MINUTE) BETWEEN ? AND ?")), + wantQuery: "SELECT `id` FROM `events` WHERE DATE_ADD(date, INTERVAL duration MINUTE) BETWEEN ? AND ?", + wantArgs: []interface{}{"2022-05-03", "2022-05-04"}, + }, { input: func() Querier { t1, t2 := Table("users").Schema("s1"), Table("pets").Schema("s2") From 80212ca0be16a7a8d9c456180aaef9d4394304c1 Mon Sep 17 00:00:00 2001 From: Hafiz Shafruddin Date: Fri, 6 May 2022 13:03:29 +1000 Subject: [PATCH 03/10] move custom predicate example and show examples using both P() and ExprP() --- dialect/sql/builder_test.go | 24 ++++++++-- doc/md/predicates.md | 92 ++++++++++++++++++++++--------------- 2 files changed, 77 insertions(+), 39 deletions(-) diff --git a/dialect/sql/builder_test.go b/dialect/sql/builder_test.go index 9a36ff7a33..09719ed1b2 100644 --- a/dialect/sql/builder_test.go +++ b/dialect/sql/builder_test.go @@ -1473,12 +1473,30 @@ func TestBuilder(t *testing.T) { wantArgs: []interface{}{1}, }, { - input: Select("id").From(Table("users")).Where(ExprP("Date(last_login_at) >= ?")), - wantQuery: "SELECT `id` FROM `users` WHERE Date(last_login_at) >= ?", + input: Select("id").From(Table("users")).Where(ExprP("DATE(last_login_at) >= ?", "2022-05-03")), + wantQuery: "SELECT `id` FROM `users` WHERE DATE(last_login_at) >= ?", wantArgs: []interface{}{"2022-05-03"}, }, { - input: Select("id").From(Table("events")).Where(ExprP("DATE_ADD(date, INTERVAL duration MINUTE) BETWEEN ? AND ?")), + input: Select("id"). + From(Table("users")). + Where(P(func(b *Builder) { + b.WriteString("DATE(").Ident("last_login_at").WriteString(") >= ").Arg("2022-05-03") + })), + wantQuery: "SELECT `id` FROM `users` WHERE DATE(`last_login_at`) >= ?", + wantArgs: []interface{}{"2022-05-03"}, + }, + { + input: Select("id").From(Table("events")).Where(ExprP("DATE_ADD(date, INTERVAL duration MINUTE) BETWEEN ? AND ?", "2022-05-03", "2022-05-04")), + wantQuery: "SELECT `id` FROM `events` WHERE DATE_ADD(date, INTERVAL duration MINUTE) BETWEEN ? AND ?", + wantArgs: []interface{}{"2022-05-03", "2022-05-04"}, + }, + { + input: Select("id"). + From(Table("events")). + Where(P(func(b *Builder) { + b.WriteString("DATE_ADD(date, INTERVAL duration MINUTE) BETWEEN ").Arg("2022-05-03").WriteString(" AND ").Arg("2022-05-04") + })), wantQuery: "SELECT `id` FROM `events` WHERE DATE_ADD(date, INTERVAL duration MINUTE) BETWEEN ? AND ?", wantArgs: []interface{}{"2022-05-03", "2022-05-04"}, }, diff --git a/doc/md/predicates.md b/doc/md/predicates.md index cc23354e4a..abe0ae71bb 100755 --- a/doc/md/predicates.md +++ b/doc/md/predicates.md @@ -89,6 +89,62 @@ client.Pet. Custom predicates can be useful if you want to write your own dialect-specific logic or to control the executed queries. +For example, if you want to use builtin sql functions such as a `DATE()` function to a `WHERE` statement. +It can be achieved by using either `P()` or `ExprP()`. + +1. `P()` + +Using `P()` gives more flexibility in how you arrange the strings. + +```go +Select("id"). +From(Table("users")). +Where(P(func(b *Builder) { + b.WriteString("DATE(").Ident("last_login_at").WriteString(") >= ").Arg("2022-05-03") +})) +``` + +The above code will produce the following SQL query: + +```sql +SELECT `id` FROM `users` WHERE DATE(`last_login_at`) >= ? +``` + +2. `ExprP()` + +The same thing can be achieved with + +```go +users := client.User.Query(). + Where(func(s *ent.Selector) { + s.Where(sql.ExprP("DATE(last_login_at >= ?", dateVar)) + }). + AllX(ctx) +``` + +The above code will produce the following SQL query: + +```sql +SELECT `users`.`id`, `users`.`last_login_at` FROM users WHERE DATE(last_login_at) <= ?; +``` + +You may also perform a more complex sql query, for example `DATE_ADD()` + +```go +events := r.ent.Event.Query(). + Where(func(s *entsql.Selector) { + s.Where(entsql.ExprP("DATE_ADD(date, INTERVAL duration MINUTE) BETWEEN ? AND ?", startDateVar, endDateVar)) + }). + AllX(ctx) +``` + +The above code will produce the following SQL query: + +```sql +SELECT `events`.`id`, `events`.`date`, `events`.`duration` +FROM events WHERE DATE_ADD(date, INTERVAL duration MINUTE) BETWEEN ? AND ?; +``` + #### Get all pets of users 1, 2 and 3 ```go @@ -205,42 +261,6 @@ The above code will produce the following SQL query: SELECT DISTINCT `pets`.`id`, `pets`.`owner_id`, `pets`.`name`, `pets`.`age`, `pets`.`species` FROM `pets` WHERE `name` LIKE '_B%' ``` -#### Escape hatch for `WHERE` - -Sometimes you want to use builtin sql functions such as a `DATE()` function to a `WHERE` statement. -It can be achieved by using `ExprP()`. - -```go -users := client.User.Query(). - Where(func(s *ent.Selector) { - s.Where(sql.ExprP("DATE(last_login_at >= ?", dateVar)) - }). - AllX(ctx) -``` - -The above code will produce the following SQL query: - -```sql -SELECT `users`.`id`, `users`.`last_login_at` FROM users WHERE DATE(last_login_at) <= ?; -``` - -You may also perform a more complex sql query, for example `DATE_ADD()` - -```go -events := r.ent.Event.Query(). - Where(func(s *entsql.Selector) { - s.Where(entsql.ExprP("DATE_ADD(date, INTERVAL duration MINUTE) BETWEEN ? AND ?", startDateVar, endDateVar)) - }). - AllX(ctx) -``` - -The above code will produce the following SQL query: - -```sql -SELECT `events`.`id`, `events`.`date`, `events`.`duration` -FROM events WHERE DATE_ADD(date, INTERVAL duration MINUTE) BETWEEN ? AND ?; -``` - ## JSON predicates JSON predicates are not generated by default as part of the code generation. However, ent provides an official package From 3caeabf3e8cce546e3becd74356be981690dc87d Mon Sep 17 00:00:00 2001 From: Hafiz Shafruddin Date: Sun, 29 May 2022 12:11:05 +1000 Subject: [PATCH 04/10] fix to greater or equal than --- doc/md/predicates.md | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/md/predicates.md b/doc/md/predicates.md index abe0ae71bb..6e76bad81b 100755 --- a/doc/md/predicates.md +++ b/doc/md/predicates.md @@ -125,7 +125,7 @@ users := client.User.Query(). The above code will produce the following SQL query: ```sql -SELECT `users`.`id`, `users`.`last_login_at` FROM users WHERE DATE(last_login_at) <= ?; +SELECT `users`.`id`, `users`.`last_login_at` FROM users WHERE DATE(last_login_at) >= ?; ``` You may also perform a more complex sql query, for example `DATE_ADD()` From 027fb458a975c859e5d29ee82d27c78dbbe4fa82 Mon Sep 17 00:00:00 2001 From: Hafiz Shafruddin Date: Wed, 15 Jun 2022 15:15:14 +1000 Subject: [PATCH 05/10] rephrase wording and fix sql builder --- doc/md/predicates.md | 48 +++++++++++++++++++++----------------------- 1 file changed, 23 insertions(+), 25 deletions(-) diff --git a/doc/md/predicates.md b/doc/md/predicates.md index 6e76bad81b..7f61e99b61 100755 --- a/doc/md/predicates.md +++ b/doc/md/predicates.md @@ -89,19 +89,16 @@ client.Pet. Custom predicates can be useful if you want to write your own dialect-specific logic or to control the executed queries. -For example, if you want to use builtin sql functions such as a `DATE()` function to a `WHERE` statement. -It can be achieved by using either `P()` or `ExprP()`. +For example, in order to use built-in SQL functions such as `DATE()`, use one of the following options: -1. `P()` - -Using `P()` gives more flexibility in how you arrange the strings. +1. Pass a dialect-aware predicate function using the `sql.P` option: ```go -Select("id"). -From(Table("users")). -Where(P(func(b *Builder) { - b.WriteString("DATE(").Ident("last_login_at").WriteString(") >= ").Arg("2022-05-03") -})) +users := client.User.Query(). + Select("id"). + Where(P(func(b *Builder) { + b.WriteString("DATE(").Ident("last_login_at").WriteByte(')').WriteOp(OpGTE).Arg(value) + })) ``` The above code will produce the following SQL query: @@ -110,39 +107,40 @@ The above code will produce the following SQL query: SELECT `id` FROM `users` WHERE DATE(`last_login_at`) >= ? ``` -2. `ExprP()` - -The same thing can be achieved with +2. Inline a predicate expression using the `ExprP()` option: ```go users := client.User.Query(). - Where(func(s *ent.Selector) { - s.Where(sql.ExprP("DATE(last_login_at >= ?", dateVar)) - }). - AllX(ctx) + Select("id"). + Where(func(s *sql.Selector) { + s.Where(sql.ExprP("DATE(last_login_at >= ?", value)) + }). + AllX(ctx) ``` -The above code will produce the following SQL query: +But note that `last_login_at` is now hardcoded, unlike the option number 1. + +The above code will produce the same SQL query: ```sql -SELECT `users`.`id`, `users`.`last_login_at` FROM users WHERE DATE(last_login_at) >= ?; +SELECT `id` FROM `users` WHERE DATE(`last_login_at`) >= ? ``` You may also perform a more complex sql query, for example `DATE_ADD()` ```go -events := r.ent.Event.Query(). - Where(func(s *entsql.Selector) { - s.Where(entsql.ExprP("DATE_ADD(date, INTERVAL duration MINUTE) BETWEEN ? AND ?", startDateVar, endDateVar)) - }). - AllX(ctx) +events := client.Event.Query(). + Where(func(s *sql.Selector) { + s.Where(sql.ExprP("DATE_ADD(date, INTERVAL duration MINUTE) BETWEEN ? AND ?", start, end)) + }). + AllX(ctx) ``` The above code will produce the following SQL query: ```sql SELECT `events`.`id`, `events`.`date`, `events`.`duration` -FROM events WHERE DATE_ADD(date, INTERVAL duration MINUTE) BETWEEN ? AND ?; +FROM events WHERE DATE_ADD(date, INTERVAL duration MINUTE) BETWEEN ? AND ? ``` #### Get all pets of users 1, 2 and 3 From 02c7b9fe628227310d8216b8e2904f38945e744a Mon Sep 17 00:00:00 2001 From: Hafiz Shafruddin Date: Fri, 17 Jun 2022 09:24:43 +1000 Subject: [PATCH 06/10] Update doc/md/predicates.md Co-authored-by: Ariel Mashraki <7413593+a8m@users.noreply.github.com> --- doc/md/predicates.md | 5 +++-- 1 file changed, 3 insertions(+), 2 deletions(-) diff --git a/doc/md/predicates.md b/doc/md/predicates.md index 7f61e99b61..a81eeeba2b 100755 --- a/doc/md/predicates.md +++ b/doc/md/predicates.md @@ -96,9 +96,10 @@ For example, in order to use built-in SQL functions such as `DATE()`, use one of ```go users := client.User.Query(). Select("id"). - Where(P(func(b *Builder) { + Where(sql.P(func(b *sql.Builder) { b.WriteString("DATE(").Ident("last_login_at").WriteByte(')').WriteOp(OpGTE).Arg(value) - })) + })). + AllX(ctx) ``` The above code will produce the following SQL query: From 41e71933fe54af6d2f02ac2751e3b43cbac24f8b Mon Sep 17 00:00:00 2001 From: Ariel Mashraki <7413593+a8m@users.noreply.github.com> Date: Wed, 29 Jun 2022 11:23:11 +0300 Subject: [PATCH 07/10] Update doc/md/predicates.md --- doc/md/predicates.md | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/md/predicates.md b/doc/md/predicates.md index a81eeeba2b..9b5ab53e66 100755 --- a/doc/md/predicates.md +++ b/doc/md/predicates.md @@ -95,7 +95,7 @@ For example, in order to use built-in SQL functions such as `DATE()`, use one of ```go users := client.User.Query(). - Select("id"). + Select(user.FieldID). Where(sql.P(func(b *sql.Builder) { b.WriteString("DATE(").Ident("last_login_at").WriteByte(')').WriteOp(OpGTE).Arg(value) })). From 5efada34a6ddd56bfdf3743c2b738d4a0de01293 Mon Sep 17 00:00:00 2001 From: Ariel Mashraki <7413593+a8m@users.noreply.github.com> Date: Wed, 29 Jun 2022 11:23:21 +0300 Subject: [PATCH 08/10] Update doc/md/predicates.md --- doc/md/predicates.md | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/md/predicates.md b/doc/md/predicates.md index 9b5ab53e66..017b97a1e6 100755 --- a/doc/md/predicates.md +++ b/doc/md/predicates.md @@ -112,7 +112,7 @@ SELECT `id` FROM `users` WHERE DATE(`last_login_at`) >= ? ```go users := client.User.Query(). - Select("id"). + Select(user.FieldID). Where(func(s *sql.Selector) { s.Where(sql.ExprP("DATE(last_login_at >= ?", value)) }). From 7fc7c265a0b98cf6d3f27dd7e372547dbea85038 Mon Sep 17 00:00:00 2001 From: Ariel Mashraki <7413593+a8m@users.noreply.github.com> Date: Wed, 29 Jun 2022 11:23:30 +0300 Subject: [PATCH 09/10] Update doc/md/predicates.md --- doc/md/predicates.md | 2 -- 1 file changed, 2 deletions(-) diff --git a/doc/md/predicates.md b/doc/md/predicates.md index 017b97a1e6..d7ae956393 100755 --- a/doc/md/predicates.md +++ b/doc/md/predicates.md @@ -119,8 +119,6 @@ users := client.User.Query(). AllX(ctx) ``` -But note that `last_login_at` is now hardcoded, unlike the option number 1. - The above code will produce the same SQL query: ```sql From ef321877d4af8583b8a709030c00929b2bace26c Mon Sep 17 00:00:00 2001 From: Ariel Mashraki <7413593+a8m@users.noreply.github.com> Date: Wed, 29 Jun 2022 11:23:38 +0300 Subject: [PATCH 10/10] Update doc/md/predicates.md --- doc/md/predicates.md | 18 ------------------ 1 file changed, 18 deletions(-) diff --git a/doc/md/predicates.md b/doc/md/predicates.md index d7ae956393..74957b03e3 100755 --- a/doc/md/predicates.md +++ b/doc/md/predicates.md @@ -124,24 +124,6 @@ The above code will produce the same SQL query: ```sql SELECT `id` FROM `users` WHERE DATE(`last_login_at`) >= ? ``` - -You may also perform a more complex sql query, for example `DATE_ADD()` - -```go -events := client.Event.Query(). - Where(func(s *sql.Selector) { - s.Where(sql.ExprP("DATE_ADD(date, INTERVAL duration MINUTE) BETWEEN ? AND ?", start, end)) - }). - AllX(ctx) -``` - -The above code will produce the following SQL query: - -```sql -SELECT `events`.`id`, `events`.`date`, `events`.`duration` -FROM events WHERE DATE_ADD(date, INTERVAL duration MINUTE) BETWEEN ? AND ? -``` - #### Get all pets of users 1, 2 and 3 ```go