Add docs on using ExprP() for custom WHERE sql statement #2520

gmhafiz · 2022-05-03T13:17:45Z

The ExprP() function is documented in builder.go and includes an example.

This pull request intends to make ExprP() visible in the website and provides two additional examples using sql date functions.

gmhafiz · 2022-05-04T04:39:13Z

from slack discussion: https://app.slack.com/client/T029RQSE6/C01FMSQDT53/thread/C01FMSQDT53-1651387195.136259

a8m · 2022-05-05T07:03:21Z

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`


I'd merge it with the custom predicate section above. Also, the ExprP is one way of doing it, you can also achieve the same thing with P(func(b *sql.Builder) { ... }) - which gives more control over how identifiers and placeholders are formatted. I can suggest another example option for writing it if you like.

Like this?

{ input: Select("id").From(Table("users")).Where(P(func(b *Builder) { b.WriteString("DATE(last_login_at) >= ?").Arg("2022-05-03") })), wantQuery: "SELECT `id` FROM `users` WHERE DATE(last_login_at) >= ?", wantArgs: []interface{}{"2022-05-03"}, },

I think it should be this instead

{ 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"}, },

…prP()

gmhafiz · 2022-05-06T03:19:57Z

@a8m I've added examples using both P() and ExprP() to docs and builder_test.go

gmhafiz · 2022-05-29T07:18:05Z

Looks like a timeout issue in the lint?

run golangci-lint
  Running [/home/runner/golangci-lint-1.45.2-linux-amd64/golangci-lint run --out-format=github-actions] in [] ...
  level=error msg="Timeout exceeded: try increasing it by passing --timeout option"
  
  Error: golangci-lint exit with code 4
  Ran golangci-lint in 196379ms

a8m · 2022-05-29T13:26:15Z

doc/md/predicates.md

+You may also perform a more complex sql query, for example `DATE_ADD()`
+
+```go
+events := r.ent.Event.Query().


Suggested change

events := r.ent.Event.Query().

events := client.Event.Query().

a8m · 2022-05-29T13:26:30Z

doc/md/predicates.md

+```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))


Suggested change

s.Where(entsql.ExprP("DATE_ADD(date, INTERVAL duration MINUTE) BETWEEN ? AND ?", startDateVar, endDateVar))

s.Where(entsql.ExprP("DATE_ADD(date, INTERVAL duration MINUTE) BETWEEN ? AND ?", start, end))

a8m · 2022-05-29T13:27:03Z

doc/md/predicates.md

+
+```go
+events := r.ent.Event.Query().
+    Where(func(s *entsql.Selector) {


Suggested change

Where(func(s *entsql.Selector) {

Where(func(s *sql.Selector) {

a8m · 2022-05-29T13:28:30Z

doc/md/predicates.md

+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()`.


Suggested change

10000

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:

a8m · 2022-05-29T13:30:55Z

doc/md/predicates.md

+1. `P()`
+
+Using `P()` gives more flexibility in how you arrange the strings.


Suggested change

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:

a8m · 2022-05-29T13:34:23Z

doc/md/predicates.md

+Select("id").
+From(Table("users")).
+Where(P(func(b *Builder) {
+    b.WriteString("DATE(").Ident("last_login_at").WriteString(") >= ").Arg("2022-05-03")


Suggested change

b.WriteString("DATE(").Ident("last_login_at").WriteString(") >= ").Arg("2022-05-03")

b.WriteString("DATE(").Ident("last_login_at").WriteByte(')').WriteOp(OpGTE).Arg(value)

a8m · 2022-05-29T13:35:04Z

doc/md/predicates.md

+Select("id").
+From(Table("users")).
+Where(P(func(b *Builder) {


Align this example with the one below.

a8m · 2022-05-29T13:35:36Z

doc/md/predicates.md

+```go
+users := client.User.Query().
+    Where(func(s *ent.Selector) {
+        s.Where(sql.ExprP("DATE(last_login_at >= ?", dateVar))		


Suggested change

s.Where(sql.ExprP("DATE(last_login_at >= ?", dateVar))

s.Where(sql.ExprP("DATE(last_login_at >= ?", value))

a8m · 2022-05-29T13:35:50Z

doc/md/predicates.md

+
+```go
+users := client.User.Query().
+    Where(func(s *ent.Selector) {


Suggested change

Where(func(s *ent.Selector) {

Where(func(s *sql.Selector) {

a8m · 2022-05-29T13:36:52Z

doc/md/predicates.md

+2. `ExprP()`
+
+The same thing can be achieved with


Suggested change

2. `ExprP()`

The same thing can be achieved with

2\. Inline a predicate expression using the `ExprP()` option:

Hi @a8m, thank you for all the feedback. I have made the changes as you suggested.

However for this, I do not understand the wording Invalid a predicate expression.... Are we invalidating anything?

Sorry for the confusion. I think I meant “inline”, but accidentally wrote “invalid”.

no worries! pushed all commits for review

doc/md/predicates.md

Co-authored-by: Ariel Mashraki <7413593+a8m@users.noreply.github.com>

doc/md/predicates.md

a8m · 2022-06-29T08:25:38Z

Merged! I'm really sorry for the delayed review, and want to thank you for the contribution, @gmhafiz 🙏

Add docs on using ExprP() for custom WHERE sql statement

5b2c9d4

gmhafiz added 2 commits May 5, 2022 16:19

Add ExprP() examples using integration test

12a3e5c

Merge branch 'ent:master' into master

8cb3e0b

a8m reviewed May 5, 2022

View reviewed changes

gmhafiz added 2 commits May 6, 2022 13:03

8000

move custom predicate example and show examples using both P() and Ex…

80212ca

…prP()

Merge branch 'ent:master' into master

7da09e9

gmhafiz added 3 commits May 21, 2022 13:13

Merge branch 'ent:master' into master

789b605

Merge branch 'ent:master' into master

d893d59

fix to greater or equal than

3caeabf

a8m requested changes May 29, 2022

View reviewed changes

rephrase wording and fix sql builder

027fb45

a8m reviewed Jun 16, 2022

View reviewed changes

doc/md/predicates.md Outdated Show resolved Hide resolved

Update doc/md/predicates.md

02c7b9f

Co-authored-by: Ariel Mashraki <7413593+a8m@users.noreply.github.com>

a8m approved these changes Jun 29, 2022

View reviewed changes

doc/md/predicates.md Outdated Show resolved Hide resolved

doc/md/predicates.md Outdated Show resolved Hide resolved

doc/md/predicates.md Outdated Show resolved Hide resolved

doc/md/predicates.md Outdated Show resolved Hide resolved

a8m added 4 commits June 29, 2022 11:23

Update doc/md/predicates.md

41e7193

Update doc/md/predicates.md

5efada3

Update doc/md/predicates.md

7fc7c26

Update doc/md/predicates.md

ef32187

a8m merged commit 5dbfa24 into ent:master Jun 29, 2022

timoha mentioned this pull request Oct 7, 2022

custom window function selector timoha/ent#1

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Add docs on using ExprP() for custom WHERE sql statement #2520

Add docs on using ExprP() for custom WHERE sql statement #2520

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

	events := r.ent.Event.Query().
	events := client.Event.Query().

	s.Where(entsql.ExprP("DATE_ADD(date, INTERVAL duration MINUTE) BETWEEN ? AND ?", startDateVar, endDateVar))
	s.Where(entsql.ExprP("DATE_ADD(date, INTERVAL duration MINUTE) BETWEEN ? AND ?", start, end))

	Where(func(s *entsql.Selector) {
	Where(func(s *sql.Selector) {

		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, 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.

	b.WriteString("DATE(").Ident("last_login_at").WriteString(") >= ").Arg("2022-05-03")
	b.WriteString("DATE(").Ident("last_login_at").WriteByte(')').WriteOp(OpGTE).Arg(value)

	s.Where(sql.ExprP("DATE(last_login_at >= ?", dateVar))
	s.Where(sql.ExprP("DATE(last_login_at >= ?", value))

Add docs on using ExprP() for custom WHERE sql statement #2520

Add docs on using ExprP() for custom WHERE sql statement #2520

Uh oh!

Conversation

Uh oh!

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!