Conditional where's, orderBy's, and thenBy's

[sydsutton]

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.

type SelectQueryBuilder<'Selected, 'Mapped> with

  /// Sets the WHERE condition if applyFilter is true
  [<CustomOperation("whereIf", MaintainsVariableSpace = true)>]
  member this.WhereIf(state: QuerySource<'T, Query>, applyFilter, [<ProjectionParameter>] whereExpression) =
      if applyFilter then
          this.Where(state, whereExpression)
      else
          state

  /// Sets the ORDER BY for single column if applyOrderBy is true
  [<CustomOperation("orderByIf", MaintainsVariableSpace = true)>]
  member this.OrderByIf(state: QuerySource<'T, Query>, applyOrderBy, [<ProjectionParameter>] propertySelector) =
      if applyOrderBy then
          this.OrderBy(state, propertySelector)
      else
          state

  /// Sets the ORDER BY DESC for single column if applyOrderByDescending is true
  [<CustomOperation("orderByDescendingIf", MaintainsVariableSpace = true)>]
  member this.OrderByDescendingIf
      (
          state: QuerySource<'T, Query>,
          applyOrderByDescending,
          [<ProjectionParameter>] propertySelector
      ) =
      if applyOrderByDescending then
          this.OrderByDescending(state, propertySelector)
      else
          state

  /// Sets the ORDER BY for single column if applyThenBy is true
  [<CustomOperation("thenByIf", MaintainsVariableSpace = true)>]
  member this.ThenByIf(state: QuerySource<'T, Query>, applyThenBy, [<ProjectionParameter>] propertySelector) =
      if applyThenBy then
          this.ThenBy(state, propertySelector)
      else
          state

  /// Sets the ORDER BY DESC for single column if applyThenByDescending is true
  [<CustomOperation("thenByDescendingIf", MaintainsVariableSpace = true)>]
  member this.ThenByDescendingIf
      (
          state: QuerySource<'T, Query>,
          applyThenByDescending,
          [<ProjectionParameter>] propertySelector
      ) =
      if applyThenByDescending then
          this.ThenByDescending(state, propertySelector)
      else
          state

[JordanMarr]

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?

[sydsutton]

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>

[JordanMarr]

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.

[JordanMarr]

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)
    )
}

[JordanMarr]

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

[sydsutton]

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.

[JordanMarr]

I just saw this post by @SchlenkR about adding conditional fields:
https://bsky.app/profile/schlenkr.bsky.social/post/3ldq4mfbugc2g

Which would provide the ideal syntax for conditional where statements:

select {
    for c in main.Customer do
    
    if filters.FirstName.IsSome then
        where (c.FirstName = filters.FirstName.Value)

    if filters.LastName.IsSome then
        where (c.LastName = filters.LastName.Value)
}

I may take a stab at trying to implement this based off his code:
https://github.com/SchlenkR/FsHttp/blob/master/src/FsHttp/Dsl.CE2.fs