AutoQuery and'ing 2 groups of or's

ServiceStack Auto Query
1

Is there a way to get autoquery to produce a where clause that and’s two groups of or’s?

i.e.
where (a.col1 like ‘%a%’ or a.col2 like ‘%a%’) and (b.col1 like ‘%b%’ or b.col2 like ‘%b%’)

2

It’s not a built-in feature as AutoQuery Services filters have a default AND behavior that can be changed to use OR behavior for all or specific fields, but there’s no support for custom groups of multiple columns.

But you could use a customizable adhoc query or a custom implicit convention with a single query that utilizes multiple OR fields in a single term, e.g:

public class MyQuery : QueryDb<Table>
{
    [QueryDbField(Template = "Col1 LIKE {Value} OR Col2 LIKE {Value}", 
                  ValueFormat="%{0}%")]
    public string GroupA { get; set; }

    [QueryDbField(Template = "Col1 LIKE {Value} OR Col2 LIKE {Value}", 
                  ValueFormat="%{0}%")]
    public string GroupB { get; set; }
}
3

Does this work anymore?

My DTO:

public class QueryEmailAccount : QueryDb<AdHocEmailAccount>
{
    [QueryDbField(Template = "AccountName LIKE {Value} OR EmailAddress LIKE {Value}", ValueFormat="%{0}%")]
    public string SearchTerm { get; set; }
}

Then when I call the API, either with code or from ui/QueryEmailAccount it doesn’t filter at all. I’m outputting the SQL, so I can see there’s no WHERE clause there.

SQL: SELECT TOP 1000 "Id", "AccountName", "EmailAddress", "Description", "LastUpdated", "LastUpdatedBy", "UsedInContext" 
FROM "AdHocEmailAccount"
SQL: SELECT COUNT(*) "COUNT(*)" 
FROM "AdHocEmailAccount"

A counter-example is if I use AccountNameContains="xxx" or something, which works like expected (also the SQL output).

The examples in your documentation all use {Field} as well, but how would that work in this case?

Note:
[QueryDbField(Template = "{Field} LIKE {Value}", Field="AccountName", ValueFormat="%{0}%")] works,
[QueryDbField(Template = "AccountName LIKE {Value}", ValueFormat="%{0}%")] doesn’t work.

PS: ServiceStack 8.2.2 and .NET 8.0

4

You need to add a field, should work with any matching field, e.g:

[QueryDbField(Template = "AccountName LIKE {Value} OR Email LIKE {Value}", 
    ValueFormat="%{0}%",
    Field = "AccountName")]
public string SearchTerm { get; set; }

Edit: Updated to use Field

1 Like
5

That worked, however it should be Field and not Name.

[QueryDbField(Template = "AccountName LIKE {Value} OR Email LIKE {Value}", 
    ValueFormat="%{0}%",
    Field = "AccountName")]
public string SearchTerm { get; set; }

Thanks for the quick help, it was not intuitive to add the unused Field but with such quick support, it was also not much delay. Thanks.

1 Like