Feature suggestion: Add column alias to select builder

[travis-leith]

I have a select with a join. Both tables have a column called id. I need to select one of these ids. Would be good if I can specify an alias for one of the ids so that the select can work. I think this can be accomplished by adding something like the following to the select builder.

alias "table_name.old_column_name" "new_column_name"

This is how I have implemented in my local code for MySql

type Alias = {
  oldColumnName:string
  newColumnName:string
}

type SelectQuery = {
    Table : string
    Where : Where
    OrderBy : OrderBy list
    Pagination : Pagination
    Joins : Join list
    Aliases : Alias list
}

[<CustomOperation "alias">]
    member __.Alias (state:SelectQuery, oldColumn, newColumn) = { state with Aliases = {oldColumnName = oldColumn; newColumnName = newColumn} :: state.Aliases }

let evalSelectQuery fields meta (q:SelectQuery) =
        let aliasMap = q.Aliases |> List.map (fun a -> a.newColumnName, a.oldColumnName) |> Map.ofList
        let applyAlias newSelectField = aliasMap.TryFind newSelectField |> Option.map (fun oldSelectField -> sprintf "%s AS %s" oldSelectField (inQuotes newSelectField)) |> Option.defaultValue (inQuotes newSelectField)
        let fieldNames = fields |> List.map applyAlias|> String.concat ", "

        // basic query
        let sb = StringBuilder(sprintf "SELECT %s FROM %s" fieldNames q.Table)
        // joins
        let joins = evalJoins q.Joins
        if joins.Length > 0 then sb.Append joins |> ignore
        // where
        let where = evalWhere meta q.Where
        if where.Length > 0 then sb.Append (sprintf " WHERE %s" where) |> ignore
        // order by
        let orderBy = evalOrderBy q.OrderBy
        if orderBy.Length > 0 then sb.Append (sprintf " ORDER BY %s" orderBy) |> ignore
        // pagination
        let pagination = evalPagination q.Pagination
        if pagination.Length > 0 then sb.Append (sprintf " %s" pagination) |> ignore
        sb.ToString()

[Dzoukr]

Hi @travis-leith,

is there any issue with proposed approach like having each record for one table?

type TableOneRecord = {
    Id : int
    Name : string
}

type TableTwoRecord = {
    Id : int
    Title : string
    TableOneId : int
}

select {
    table "TableOne"
    innerJoin "TableTwo" "TableOneId" "TableOne.Id"
} |> conn.SelectAsync<TableOneRecord, TableTwoRecord>

Can you show me more from your select query to better understand the issue?

[travis-leith]

I did not know that was possible. I now see that the documentation makes it clear that this is how to do it, but somehow I missed that part. Thanks!