CODEWITHSUNDEEP
servicestack
pvieira
pvieira

Reputation: 1695

ServiceStack Customizable Adhoc Queries with multiple fields

Consider the following database table:

Start End Value
1 5 A
5 10 B
10 15 C
15 20 D
20 25 E

Consider the following request DTO:

    public class QueryTable : QueryDb<Table>
    {
        [QueryDbField(Template = "End >= {Value1} AND {Field} < {Value2}", Field = "Start")]
        public int[] Range { get; set; }
    }

And the following usage:

        var request = new QueryTable()
        {
            Range = new int[] { 6, 14 }
        };

This request should select the two records having the value B and C, which is correct.

My question is: is there a better way to create the Template in attribute "QueryDbField"?

Looks like only one field is supported (in this example "Start"), but the WHERE expression needs two fields, so one is forced to "hard-code" one the of fields ("End" in this example).

This looks like a "workaround", and I would like to know if there is a proper way to express this condition.

Upvotes: 2

Views: 51

Answers (1)

mythz
mythz

Reputation: 143349

As Between is a built-in implicit AutoQuery convention:

ImplicitConventions = new Dictionary<string, string> 
{
    //...
    {"%Between%", "{Field} BETWEEN {Value1} AND {Value2}"},
}

You'll be able to use the property convention:

[Route("/table")]
public class QueryTable : QueryDb<Table>
{
   public int[] StartBetween { get; set; }
}

That you can call with:

/table?StartBetween=6,14

If you want to use different fields then you wouldn't use BETWEEN and would just use different properties using any of the built-in GreaterThanOrEqual and LessThan conventions, e.g:

public class QueryTable : QueryDb<Table>
{
   public int? StartFrom { get; set; }
   public int? EndLessThan { get; set; }
}

That you can call as normal:

/table?StartFrom=6&EndLessThan=14

Upvotes: 1

Related Questions