Open sydsutton opened 1 year ago
Have you been using these as extensions methods in your own project yet?
I'm curious to know if using where
+ whereIf true
side-by-side in the same query works properly, specifically regarding the parentheses / nesting of the where statements. I'm sure that people would try to use them together, so it would need to handle the parentheses properly in that case.
Can you print out an example of the generated SQL query in the case?
When using both where and whereIf in the same query, they're composed with AND. I added these two tests locally and they both passed: "test "whereIf true" { let query = select { for c in main.Customer do where (c.FirstName = "John") whereIf (true) (c.LastName = "Doe") } let sql = query.ToKataQuery() |> toSql Expect.equal sql "SELECT * FROM \"main\".\"Customer\" AS \"c\" WHERE (\"c\".\"FirstName\" = @p0) AND (\"c\".\"LastName\" = @p1) " ""}"
`test "whereIf false" {
let query =
select {
for c in main.Customer do
where (c.FirstName = "John")
whereIf (false) (c.LastName = "Doe")
}
let sql = query.ToKataQuery() |> toSql
Expect.equal
sql
"SELECT * FROM \"main\".\"Customer\" AS \"c\" WHERE (\"c\".\"FirstName\" = @p0)"
""}`
[11:35:22 DBG] Sqlite.Query Unit Tests.whereIf true starting... <Expecto> [11:35:22 DBG] Sqlite.Query Unit Tests.whereIf true passed in 00:00:00. <Expecto> [11:35:22 DBG] Sqlite.Query Unit Tests.whereIf false starting... <Expecto> [11:35:22 DBG] Sqlite.Query Unit Tests.whereIf false passed in 00:00:00. <Expecto>
The reason I am hesitant to add these methods to the builder is that I would like to avoid an explosion of subtle variations of where___
and orderBy___
methods, especially considering that users can opt to add these kinds of methods to the builder as extensions if they really need them.
First we add whereIf
which generates an (<condition1>) AND (<condition2>)
, and then someone asks for an OR
variation.
So I think that it would be better to create one extra where
that would have all the configuration options.
Maybe something like this:
select {
for c in main.Customer do
where (filters.LastName.IsSome) (Where.And) (c.LastName = filters.LastName.Value)
where (filters.FirstName.IsSome) (Where.And) (c.FirstName = filters.FirstName.Value)
Which admittedly is kind of ugly.
Here is another possible variation of the conditional where clause that looks pretty clean:
select {
for c in main.Customer do
where (
(filters.LastName.IsSome && c.LastName = filters.LastName.Value) &&
(filters.FirstName.IsSome && c.FirstName = filters.FirstName.Value)
)
}
My apologies for dragging my feet on this for so long.
While I'm mulling this design idea around, I am adding a new feature in the meantime that provides direct access to the underlying SqlKata.Query
object via the kata
operation.
This will make it pretty easy to add conditional where and order by clauses, as well as many other query customizations.
https://github.com/JordanMarr/SqlHydra/releases/tag/query-v2.0.2
No reason to apologize- I've been the one that's been too busy to respond. Do whatever you feel is best! Thanks for the continuous work you put into it.
I saw this issue a while ago and thought I'd contribute. If you'd like a PR instead, I'd be happy to send one over.