-
Notifications
You must be signed in to change notification settings - Fork 5
Transforming query results
The query result is always tabular. Even though ADO.NET allows to execute many SQL commands at once, their in-memory representation form much more complex, hierarchical structures.
Fortunately, queries are functions and as such they can participate in functional composition. There is no need to prepare any complex, reflection-based mechanisms, only standard >>
and |>
operators are needed.
Most transformations fall into four categories.
Function, returning two results, must return tuple:
let getPostsWithTags: int -> DataContext -> Post * Tag list =
sql "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status from post where blogId = @id;
select t.postId, t.name from tag t join post p on t.postId = p.id where p.blogId = @id"
The result can be transformed with the join
function:
let getPostsWithTags: int -> DataContext -> Post list =
sql "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status from post where blogId = @id;
select t.postId, t.name from tag t join post p on t.postId = p.id where p.blogId = @id"
>> join (fun p -> p.id) (fun t -> t.postId) (fun p t -> { p with tags = t })
|> curry
One drawback of the usage of >>
operator is, that it accepts one-arg functions only. Therefore, a call to the curry
function is needed to make possible use of two parameters.
The query function may be async. In this case, the transformation looks quite similar:
let getPostsWithTags: int -> DataContext -> Post list Async =
sql "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status from post where blogId = @id;
select t.postId, t.name from tag t join post p on t.postId = p.id where p.blogId = @id"
>> mapAsync (join (fun p -> p.id) (fun t -> t.postId) (fun p t -> { p with tags = t }))
|> curry
When following some simple naming conventions, i.e.
- the join key has
Id
,<Parent>Id
or '_id` name in parent record (case insensitive), - the join key has
<Parent>Id
or<Parent>_id
in child record (case insensitive), - exactly one proprty of
<Child>
list type exists in parent record,
the more concise join version can be used:
let getPostsWithTags: int -> DataContext -> Post list Async =
sql "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status from post where blogId = @id;
select t.postId, t.name from tag t join post p on t.postId = p.id where p.blogId = @id"
>> mapAsync Join<Post>.Left
|> curry
Sometimes more, than two results should be joined. In this case we can use a mechanism, that combine joins:
let getPostsWithTagsAndComments: int -> DataContext -> Post list =
sql "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status from post where blogId = @id;
select t.postId, t.name from tag t join post p on t.postId = p.id where p.blogId = @id;
select c.id, c.postId, c.parentId, c.content, c.author, c.createdAt from comment c join post p on c.postId = p.id where p.blogId = @id"
>> Results
.Transform(Join<Tag>.Left)
.Transform(join (fun p -> p.id) (fun c -> c.postId) (fun p c -> { p with comments = Tooling.buildTree c} ))
.Combine
|> curry
The Results.Transform
builds an object containing function, that performs first join. The next call to Transform
extends this function to transform next tuple result. The function can be further extended to process even bigger tuples. The Combine
method returns final result transformation function.
Combination of transformations can be specified in more hapster way:
let getPostsWithTagsAndComments: int -> DataContext -> Post list =
sql "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status from post where blogId = @id;
select t.postId, t.name from tag t join post p on t.postId = p.id where p.blogId = @id;
select c.id, c.postId, c.parentId, c.content, c.author, c.createdAt from comment c join post p on c.postId = p.id where p.blogId = @id"
>> Results()
>- Join<Tag>.Left
>- (join (fun p -> p.id) (fun c -> c.postId) (fun p c -> { p with comments = Tooling.buildTree c} ))
|> Results.Combine
|> curry
The result, that is a product, can be represented as a list of tuples:
let getPostsWithTags: int -> DataContext -> (Post * Tag) list =
sql "select p.id, p.blogId, p.name, p.title, p.content, p.author, p.createdAt, p.modifiedAt, p.modifiedBy, p.status,
t.postId as item_postId, t.name as item_name
from post p left join tag t on t.postId = p.id
where p.id = @id"
It can be transformed using grouping by first item of a tuple:
let getPostsWithTags: int -> DataContext -> Post list =
sql "select p.id, p.blogId, p.name, p.title, p.content, p.author, p.createdAt, p.modifiedAt, p.modifiedBy, p.status,
t.postId as item_postId, t.name as item_name
from post p left join tag t on t.postId = p.id
where p.id = @id"
>> group (fun p t -> { p with tags = aliasedAsItem t })
|> curry
In the example above, there are two 'name' columns. To make it possible to use aliasing, we use aliasedAsItem
function, that allows to use item_{column name} aliases for detail data. There are also group3
and group4
functions.
When reading one object and some detail data, no joining or grouping is needed. In this case, we can use ordinary function:
let getBlogWithPosts: int -> DataContext -> Blog =
sql "select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy
from Blog
where id = @id;
select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status
from post
where blogId = @id"
>> (fun b p -> { b with posts = p })
|> curry
As with join, when exactly one property of <Child> list
exists, more concise way can be used:
let getBlogWithPosts: int -> DataContext -> Blog =
sql "select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy
from Blog
where id = @id;
select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status
from post
where blogId = @id"
>> Update<Post>.Left
|> curry
There are two functions transforming tuples, returned from stored procedures:
resultOnly (map: 't -> 'u) (_: int, (), result: 't)
that ignores return code and output parameters, and returns query results with possible mapping function, and:
outParamsOnly (_: int, outParams, ())
that ignores return code and results, and returns output parameters. They can be called the same way, as other transformations:
let findPosts: (PostSearchCriteria * SignatureSearchCriteria) -> DataContext -> Post list =
storedproc "FindPosts"
>> resultOnly id
|> curry
or in asynchronous mode:
let findPosts: (PostSearchCriteria * SignatureSearchCriteria) -> DataContext -> Post list Async =
storedproc "FindPosts"
>> mapAsync (resultOnly id)
|> curry