CODEWITHSUNDEEP
servicestackormlite-servicestack
Guerrilla
Guerrilla

Reputation: 14906

Select distinct on joined table

I have this query

var q = Db
    .From<Blog>()
    .LeftJoin<BlogToBlogCategory>()
    .Join<BlogToBlogCategory, BlogCategory>()
    .Where( .. )
    .SelectDistinct();

var results = Db.Select<BlogCategory>(q);

It generates this SQL:

SELECT DISTINCT "blog".*, 0 EOT, "blog_category".*, 0 EOT 
FROM "blog" LEFT JOIN "blog_to_blog_category" ON
("blog"."id" = "blog_to_blog_category"."blog_id") INNER JOIN "blog_category" ON
("blog_category"."id" = "blog_to_blog_category"."blog_category_id")
WHERE ...

I want to select distinct blog_category but the select is adding all the blog fields also so I am getting duplicate blog_category entries.

How do I just select distint the joined table fields?

Upvotes: 1

Views: 86

Answers (1)

mythz
mythz

Reputation: 143349

In OrmLite the SqlExpression is what builds the SQL query you want executed:

var q = Db
    .From<Blog>()
    .LeftJoin<BlogToBlogCategory>()
    .Join<BlogToBlogCategory, BlogCategory>()
    .Where( .. )
    .SelectDistinct();

Whilst the API use to execute the query maps the result back to the specified model, in this case you're saying you want the results of the above query mapped to BlogCategory POCOs:

var results = Db.Select<BlogCategory>(q);

But you need to make sure that the query you create returns results that can map to the POCO you've specified.

If you only want to select distinct BlogCategory columns you'll need to do this in your SqlExpression:

var q = Db
    .From<Blog>()
    .LeftJoin<BlogToBlogCategory>()
    .Join<BlogToBlogCategory, BlogCategory>()
    .Where( .. )
    .SelectDistinct<BlogCategory>(c => c);

Or if you want to select columns across different joined tables you'd use a standard anonymous type expression:

var q = Db
    .From<Blog>()
    .LeftJoin<BlogToBlogCategory>()
    .Join<BlogToBlogCategory, BlogCategory>()
    .Where( .. )
    .SelectDistinct<Blog,BlogCategory>((b,c) => new { b.Id, c.Name, ... });

Then the custom model you map the results to should have properties that match the returned columns:

var results = db.Select<BlogCategoryResult>(q);

Alternatively you can access the custom result set with the Dynamic Result Set APIs.

Upvotes: 1

Related Questions