AutoQuery returns no data with [QueryDb(QueryTerm.Or)] attribute when no OR condition is specified

ServiceStack Auto Query
1

Hello,

I have few use-cases, where I have to filter data based on several OR conditions with single value from search field.

Basically, I have a table, let’s say

[Alias("Customers"])
public class Customer
{
    [PrimaryKey]
    public long Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Company { get; set; }
    public string City { get; set; }
}

I am using AutoQuery with [QueryDb(QueryTerm.Or)] attribute on DTO.

[QueryDb(QueryTerm.Or)]
[Route("/api/customers", "GET")]
public class GetCustomers : QueryDb<Customer> { }

This works great, if there are any conditions specified, let’s say I query:

/api/customers?FirstNameContains=value&LastNameContains=value

And it returns all rows where FirstName or LastName Contains value.

However, when I don’t enter any conditions, I would like to return all values.

/api/customers?FirstNameContains=&LastNameContains=

This does not work, because AutoQuery creates WHERE 0=1 expression in OrmLite WhereExpression, which causes table to return no data.

I don’t know, if this is by design, or not. If it is by design, is there any workaround? Or would it be possible to add new QueryTerm like QueryTerm.OrAll, that would not create default WHERE expression and return all data when no conditions are specified?

Thanks!

2

When you use [QueryDb(QueryTerm.Or)] you’re inverting it to an opt-in “whitelist” query, where adding more conditions expands the resultset instead of filtering it, so intuitively if you don’t specify any conditions it shouldn’t return any results.

Personally I would create a new API with filtering semantics, e.g:

[Route("/customers/search")] //"GET" is implied in QueryDb
public class SearchCustomers : QueryDb<Customer> { }

Where it starts with an an unfiltered resultset and adding more conditions filters it.

custom user-defined routes shouldn’t use /api since it conflicts with the predefined /api route

Without any [Route] your APIs are available using its unique Request DTO name, e.g:

/api/SearchCustomers

Alternatively you should just be able to add a condition that returns true, e.g. ?All=1

[QueryDb(QueryTerm.Or)]
public class GetCustomers : QueryDb<Customer> 
{ 
    [QueryDbField(Operand = ">=", Field = "Id")]
    public int All { get; set; }
}
1 Like
3

Thank you, the property with condition that returns true will be sufficient for my use-case.