Support dynamic where clauses

[go-aegian]

apart from the predefined where clause, it needs somehow to be able to support passing a string containing a filter clause that it's parameters are already resolved prior to the Query function, I proposed a possible solution to be able to declare in the sql definition something like this

-- name: ListOrders :many
  select * from order where status = $1 and (@filter::text);

Generated

const listOrders = "select * from order where status = $1 and (@filter::text);"

 type ListOrdersParams struct {
       Status string
       Filter string
}

func (q *Queries) ListOrders(ctx context.Context, arg ListOrdersParams) ([]*ListOrdersRow, error) {
        filter:=arg.Filter
        if filter=="" {
           filter = "1=1"
        }
        listOrdersWithFilter = strings.Replace(listOrders, "@filter::text", filter, -1)

    rows, err := q.db.Query(ctx, listOrdersWithFilter, arg.Status)

...

What database engines need to be changed?

PostgreSQL, MySQL

What programming language backends need to be changed?

Go, Python, Kotlin

[jwc-clinnection]

I have implemented this in #2343

[go-aegian]

Thank you @jwc-clinnection once the release is available I'll check it out

[jwc-clinnection]

It is failing some tests. For example this is an expected change given the pull request:

                -               rows, err := q.db.Query(ctx, joinTextArray)
                +               sql := joinTextArray
                +       
                +               rows, err := q.db.Query(ctx, sql)

I tried:

make build-endtoend
cd ./internal/endtoend/testdata && go build ./...

But, I still get errors when I run:

make test

Should I be doing something to regenerate the test samples? If so, may I have some guidance on the procedure?

[jwc-clinnection]

Thank you @jwc-clinnection once the release is available I'll check it out

@go-aegian Can you give any direction? Thank you.

[go-aegian]

@jwc-clinnection unfortunately I can't help you on this as I am not familiar how is it compiled, tested. That is why I wait for a released version.

[jwc-clinnection]

@kyleconroy The tests that this is failing are expected given the changes. Can you give me advice as to how to proceed to correct the tests?

[iancharters]

Any movement on this? I would really like to be able to leverage this in our codebases that use sqlc.

[go-aegian]

@jwc-clinnection have you got any advice from @kyleconroy on how to move this forward to the release?

In the meantime I did manage a workaround process until your work is released though.

[victoraugustolls]

@kyleconroy, any news here? I'm open to help in any way I can. Dynamic filters are a really useful feature that would allow us to use sqlc in some queries that we opt to use squirrel instead.

[emilecaron]

@jwc-clinnection have you got any advice from @kyleconroy on how to move this forward to the release?

In the meantime I did manage a workaround process until your work is released though.

would you care to elaborate on this workaround ?