CODEWITHSUNDEEP
servicestackormlite-servicestack
Guerrilla
Guerrilla

Reputation: 14906

Using PostgreSQL aggregate functions with OrmLite

I am trying to figure out how to process query results with OrmLite for queries that use an aggregate function.

For instance take this query:

var q = db
    .From<Blog>(db.TableAlias("b"))
    .Join<BlogToBlogCategory>((b,btc) => b.Id == btc.BlogId, db.TableAlias("btc"))
    .GroupBy(x => x.Id)
    .Select("b.*, json_agg(btc)");
    
var values = db.Select<Tuple<Blog, List<BlogToBlogCategory>>>(q);

It generates the correct SQL:

SELECT b.*, json_agg(btc) 
FROM "blog" "b" INNER JOIN "blog_to_blog_category" "btc" ON ("b"."id" = "btc"."blog_id")
GROUP BY "b"."id"

But parsing result it gives error:

Column must be between 0 and 19

I am not sure how to parse the query results into parent table and list of sub-tables.

Is it possible?

Upvotes: 0

Views: 183

Answers (1)

mythz
mythz

Reputation: 143359

Returning JSON functions is the same as returning an additional string column. Since you can't mix Tuples with both Table Type and scalar values you would need another way to access the additional string value.

You could use parse the custom results as a dynamic result set, accessing all columns individually, but in this case you could create a custom result class with the additional string column and a helper to return the typed results, e.g:

public class BlogResult : Blog
{
    public string Json { get; set; }

    private List<BlogToBlogCategory> results;
    public List<BlogToBlogCategory> Results => 
        results ??= Json.FromJson<List<BlogToBlogCategory>>();
}

Where you can select into the custom typed model with:

var q = db
    .From<Blog>(db.TableAlias("b"))
    .Join<BlogToBlogCategory>((b,btc) => b.Id == btc.BlogId, db.TableAlias("btc"))
    .GroupBy(x => x.Id)
    .Select("b.*, json_agg(btc) as Json");
    
var values = db.Select<BlogResult>(q);

But if your PostgreSQL column names use snake_case and your C# Types use PascalCase you would need to use custom serialization that allows more lax mapping between different property style casing, e.g:

public static class CustomJsonSerializer
{
    public static T FromJson<T>(string json)
    {
        using var scope = JsConfig.With(new Config { 
            PropertyConvention = PropertyConvention.Lenient });
        return json.FromJson<T>();
    }
}

public class BlogResult : Blog
{
    public string Json { get; set; }

    private List<BlogToBlogCategory> results;
    public List<BlogToBlogCategory> Results => results ??= 
        CustomJsonSerializer.FromJson<List<BlogToBlogCategory>>(Json);
}

Upvotes: 2

Related Questions