[request]: Build dynamic query builder similar to sequel (rb)

[mguentner]

Currently statements need to be written and compiled for each request.
A query builder could help doing that by aggregating the values / parameters first and then compiling the statement.
I like the interface the Sequel project (ruby) has.

Here is a simple select with chained statements:

ds = DB[:posts]
result = ds.select(:id, :name).order(:name).where{id < 100}.all

http://sequel.jeremyevans.net/rdoc/files/doc/dataset_basics_rdoc.html
http://sequel.jeremyevans.net/documentation.html

[mguentner]

I hit the "Comment" button too soon.

[cznic]

I'm afraid this does not fit the existing QL mechanism. The linked article says this:

One of the core dataset ideas that should be understood is that datasets are frozen and use a functional style of modification, in which methods called on the dataset return modified copies of the dataset, they don't modify the dataset themselves:

The problem is, that in QL a dataset do not exists. Or more precisely, it never materializes in its entirety. QL translates the query to a function that on every call computes and returns just one row of the dataset. The nice property of a "frozen" dataset is that it can be reused and (sub)queried in different ways later, without recomputing it. That is possible to simulate in QL only by INSERT INTO foo SELECT ... to materialize the dataset and subsequently performing the (sub)queries over the foo table.

But maybe I've just misunderstood the concepts used by sequel.

[cznic]

@mguentner I'm not sure what can be done with this issue. WDYT?

[mguentner]

The initial idea was to have a query builder that helps to construct queries where the arguments can differ. Sequel was just chosen because I like the API of constructing queries.
How the actual dataset is filtered is not important.

My current use case is a function that queries a table. Some of the arguments can be nil and thus won't be used in the WHERE clause.
I currently have a base query like SELECT * FROM users and maintain a index counter for the arguments.

Example:

count := 1
basequery := "SELECT * FROM users"
query := basequery
if name != nil {
  if count == 1 {
    query += " where"
  } else if count > 1 {
    query += " and"
  }
  query = query + " name == $" + strconv.Itoa(count)
  count++
}
if email != nil {
  if count == 1 {
    query += " where"
  } else if count > 1 {
    query += " and"
  }
  query = query + " email == $" + strconv.Itoa(count)
  count++
}
// further down...
compiledQuery, err := ql.Compile(query + ";")
// even further down...

if name != nil && email != nil {
  rs, _, err = db.Execute(ctx, compiledQuery, name, email)
}
if name == nil && email != nil {
  rs, _, err = db.Execute(ctx, compiledQuery, email)
}
if name == nil && email == nil {
  rs, _, err = db.Execute(ctx, compiledQuery)
}

Instead of concatenating a query string like above, I'd like to be able to construct a builder.

users := ql.NewSelectBuilder("users") // constructs a query for the table "users"
// -> SELECT * from users where email == [email protected] and name == "John"
query, _ = users.Where("email", email).And("name", name).Compile()
//or -> SELECT * from users where name == "John"
query, _ = users.Where("name", name).Compile()
//or -> SELECT * from users
query, _ = users.Compile()

// query is now ql.List

rs, _, err = db.Execute(ctx, query)

I believe that the above is possible with ql. Thanks for your work by the way!
You can close the issue if you want or leave it open to document the requirement :)

[cznic]

@mguentner I've tried to code something in branch issue198. Please take a look and let me know IIUC the goal. The code is dirty copy pasting and not at all undocumented, but the point of your interest is perhaps mainly the test at

ql/all_test.go

Line 3953 in 0469cd0

// https://github.com/cznic/ql/issues/198

.

The new API is mostly ad hoc, so any suggestions are welcome, thanks.

[mguentner]

Awesome! This already looks really nice. Thank you.

The expression API should allow for an identity Expression that is ignored and forms the start of a chain when the chain links are not known at compile time or should be constructed imperatively:

expr := NewExpression("")
first := true
if email != nil {
  expr = expr.Start("email").Equal(email)
  first = false
}
if name != nil {
  if first {
    expr = expr.Start("name").Equal(name)
  } else {
    expr = expr.And("name").Equal(name)
  }
  first = false
}
// int16
if age != -1 {
  if first {
    expr = expr.Start("age").Equal(age)
  } else {
    expr = expr.And("age").Equal(age)
  }
  first = false
}

func (e *Expression) Start(f interface{}) *Expression    { return e.binop("", f) }

Could be enough.

Let me know if you see another way of constructing such a query using your current API since my example is just a naive continuation of the count++ example in #198 (comment)

[cznic]

@mguentner PTAL, thank you.

[mguentner]

LGTM ;)

[cznic]

Also 52dc064 updates this issue.

@mguentner I'd like to ask you to now try to use this branch for some time, say a week or so. Let's collect your experiences and iterate over any troubles you may run into. After we'll eventually think it's ready to publish, it'll be committed to the master branch. Thanks.

[mguentner]

Will do.

[cznic]

@mguentner Have you had a chance to work with the new API? If so, please provide feedback, thank you.

[mguentner]

@cznic Not yet. Needed to refactor a lot of stuff before I change the database layer again. Once I've used the new API, I will update this issue with feedback. I promise.

[cznic]

No hurry, no problem. Thanks.

[mguentner]

ql.NewSelectStmt(ql.NewField("COUNT(*)", "")).From("users").Where(...) does not work.

-> invalid expression COUNT(*)

however ql.NewSelectStmt(ql.NewField("COUNT", "")).From("users").Where(...) does work but does not make sense in SQL.

The COUNT(*) field could also be a const in the ql package as it is quite common.

[cznic]

This is working as documented. COUNT(*) is not a field name, it's an expression. This works

NewSelectStmt(NewExpression("count(*)")).From("users").Where(42)

[mguentner]

Indeed. Thanks!