Is it possible call date_trunc from PostgreSQL?

[MangelMaxime]

In SQL, I want to write something like

SELECT date_trunc('day', fsvr."CompletedTimeStamp"), fsvr."FormSpecType", count(fsvr."FormSpecType")
FROM dbo."FormStudioValuesResult" AS fsvr
WHERE fsvr."TenantId" = '9003665c-a1c3-486e-90db-85f7a053ded8'
group by 1, fsvr."FormSpecType";

Is it possible to do something similar with SqlHydra or to have access to its internal so the user can extends it if needed ?

[JordanMarr]

There is not currently a built-in way to call SQL functions within a SqlHydra query.
However, it is possible to access and manipulate the underlying SqlKata query and create a manual SELECT clause like this:

type CityRow = { City3: string; Number: int }

let getCities() = task {
    use ctx = openContext()
            
    let! reader =
        select {
            for a in Person.Address do
            where (a.City |=| [ "Seattle"; "Denver" ])
            kata (fun q -> q.SelectRaw("SUBSTRING(City, 1, 3) AS City3, 123 AS Number"))
        }
        |> ctx.GetReaderAsync
        
    return
        [
            while reader.Read() do
                {
                    City3 = reader.Get "City3"
                    Number = reader.Get "Number"
                }
        ]
}

But you would need to get the results from the reader manually to accommodate the custom column names.

Whether or not that is better than just creating a query string and executing it manually via ADO.NET is up to you.

[MangelMaxime]

Thanks for the pointers I will give it a try.

Because, I have a lot of joins for some queries believe SqlHydra still makes senses especially because my client want a dynamic where clause. For that where close, the type safety added by SqlHydra can be worth exploring even with a manual reader.

[JordanMarr]

It looks like I don't have any examples in the README that use the GetReader method, so I should probably add this example.

Also, if you need dynamic where clauses, you may want to add a whereIf extension to allow you to pass a bool argument to dynamically apply.

type SelectBuilder<'Selected, 'Mapped> with
    /// Sets the WHERE condition if applyFilter is true.
    [<CustomOperation("whereIf", MaintainsVariableSpace = true)>]
    member this.WhereIf (state: QuerySource<'T>, (applyFilter: bool), [<ProjectionParameter>] whereExpression) = 
        let query = (state :?> QuerySource<_, _>).Query
        let querySource = QuerySource<'T, SqlKata.Query>(query, state.TableMappings)
        if applyFilter 
        then this.Where(querySource, whereExpression)
        else querySource    

Usage:

    let getMasterItems seriesId = async {
        let! rows = 
            select' cf.OpenContext {
                for mi in dbo.MASTER_ITEMS do
                join msi in dbo.MASTER_SERIES_ITEMS on (mi.ITEM_NUMBER = msi.ITEM_NUMBER)
                join ms in dbo.MASTER_SERIES on (msi.SERIES_ID = ms.SERIES_ID)
                leftJoin fd in dbo.MASTER_FITTING_DEDUCTS on (mi.ITEM_NUMBER = fd.Value.ITEM_NUMBER)
                whereIf (1 = 1) (
                    ms.SERIES_ID = seriesId &&
                    mi.ITEM_STATUS = Some "A" && // Is Active
                    mi.ITEM_CATEGORY.Value |=| [ 
                        "OPEN"; "EC"; "REC"; "EEC"; // "BFSC"; "WBC"
                        "SC"; "TRANSITION"; // "OFFSET"; "TEE"; "CROSS"
                        "IC"; "OC"; "45IC"; "45OC"; // "FL"
                    ])
                toList
            }

This is a feature that was requested twice (#2 and #59), and for some reason I never added it.
The extension method is convoluted enough that it should probably be built in.

[JordanMarr]

I have added this example to the readme:
https://github.com/JordanMarr/SqlHydra?tab=readme-ov-file#custom-sql-queries

[MangelMaxime]

Thanks for the example with the WhereIf, in my case they want the where clause to be generated based on an AST so it is a little more different so I just need to pass a normal function. :)

let dynamicCondition (mt : dbo.MemberTenant) =
	// Here there will be a logic engine to transform a DSL/AST into actual F# code
    if condition = true then
        (mt.Id = Guid.NewGuid())
    else
        (mt.AccountId = Guid.NewGuid())
    
let data =
    selectAsync HydraReader.Read (Create DbContext.create) {
        for mt in dbo.MemberTenant do
            where dynamicCondition mt
            select mt.CompletedTimeStamp
            tryHead
    }

[JordanMarr]

Ah ok.

Also, you can now pass the DbContext.create function (or an existing db context) without wrapping it in the DU and it will be implicitly converted for you (see Select Builders for more info.):

let data =
    selectAsync HydraReader.Read DbContext.create {
        for mt in dbo.MemberTenant do
            where dynamicCondition mt
            select mt.CompletedTimeStamp
            tryHead
    }

[MangelMaxime]

@JordanMarr You can close this issue if you want depending on if you think calling native SQL functions should be supported or not.