Auto Query RDBMS Distinct?

ServiceStack Auto Query
1

A user of our API needs to be able to access a list of the distinct values for a specific field. We don’t want to return the full table results to them and therefore have a limit on the result set set at 500 rows. I can’t find any documentation on how to do a select distinct via AutoQuery - is it possible? If not, why not?

thanks!

2

If you just want to return a single field you can specify which Custom Fields to return with ?Fields={Field}.

There’s no support for returning DISTINCT results, but this should be something you can do with a Custom AutoQuery Implementation by calling q.SelectDistinct(), e.g:

public object Any(QueryTableDistinct query)
{
    var q = AutoQuery.CreateQuery(query, base.Request);
    q.SelectDistinct();
   return AutoQuery.Execute(request, q);
}
3

They need a list of all distinct values of that field (Distinct). Of course we could write an additional API to return this information - there isn’t any wisdom in that. AutoQuery is so robust and supports so many other functions - is there some reason AutoQuery doesn’t support distinct?

4

DISTINCT is effectively an RDBMS-only feature which hasn’t been requested before, I also don’t like the idea of introducing an additional property on all AutoQuery DTOs for a rarely used RDBMS-only feature, but you can add any missing feature requests on UserVoice so demand for it can be measured.

If you don’t want to create a custom AutoQuery query you can try using a QueryFilter, something like this could work:

var autoQuery = new AutoQueryFeature()
  .RegisterQueryFilter<IQueryDb, Response>((q, dto, req) => {
        if (req.QueryString["distinct"] != null) q.SelectDistinct();
    });

Plugins.Add(autoQuery);

Which you could also look at adding to the AutoQuery base class.

5

Thanks. I’ve added this to the service:

var q = AutoQuery.CreateQuery(query, queryArgs, Request);
            if (queryArgs["distinct"] != null)  q.SelectDistinct();
            return AutoQuery.Execute(query, q);

But it’s not actually returning distinct records when I use the query string

?fields=CustomerId&distinct=1

In the debugger, the select statement looks to be correct, selecting only that field and selectdistinct is set to true, but it is still returning a row in the result set for each record in the table instead of a row for each distinct CustomerId. What we want is a list of the distinct CustomerIds in the table.
thanks!

6

I’ve had a look and calling .SelectDistinct() isn’t going to work because the .Select(fields) from the custom fields list overrides it, it would need to call .SelectDistinct(fields) which I’ve just added to OrmLite.

I’ve also added support for DISTINCT in AutoQuery in this commit by prefixing your fields list with DISTINCT, e.g:

/query?Fields=DISTICT Field1,Field2

This change is available from v5.0.1 that’s now available on MyGet.

7

Exceptional! Thanks!

8

Any chance of something similar being added for AutoQuery Data sources?

9

No, this feature uses RDBMS’s SELECT DISTINCT feature to handle it on the server.