Servicestack autoquery custom convention doesn't work with PostgreSQL

Question

I have defined new implicit convention

autoQuery.ImplicitConventions.Add("%WithinLastDays", "{Field} > NOW() - INTERVAL '{Value} days'");

The problem is that for postgres connection the query is wrong translated into

WHERE "TABLE"."FIELD" > NOW() - INTERVAL ':0 days'

and it doesn't send parameter to database. In case of built in conventions it works fine.

UPDATE

I was trying to define EndsWithConvention but there is the same issue - parameter is not passed to pgsql engine (however it is available in SqlExpression)

    autoQuery.EndsWithConventions
.Add("WithinLastDays", new QueryDbFieldAttribute() { Template= "{Field} >=  CURRENT_DATE - {Value}", ValueFormat= "interval '{0} days ago'" });

    autoQuery.EndsWithConventions
.Add("WithinLastDays", new QueryDbFieldAttribute() { Template= "{Field} >=  CURRENT_DATE - interval '{Value}'", ValueFormat= "{0} days ago" });

UPDATE 2 The below definition results in PostgresException: 42601: błąd składni w lub blisko "$1" (sorry for error in Polish)

autoQuery.EndsWithConventions.Add("WithinLastDays", new QueryDbFieldAttribute() { Template= "{Field} >=  CURRENT_DATE - interval {Value}", ValueFormat= "{0} days ago" });

The generated query is

  SELECT here columns
        FROM table
        WHERE table."publication_date" >=  CURRENT_DATE - interval $1
        LIMIT 100

UPDATE 3

autoQuery.EndsWithConventions.Add("WithinLastDays", new QueryDbFieldAttribute() { Template= "{Field} >=  CURRENT_DATE - {Value}", ValueFormat= "interval {0} 'days ago'" });

generates

SELECT ...
    FROM ...
    WHERE ...."publication_date" >=  CURRENT_DATE - $1

and issue PostgresException: 42883: operator doesn't exist: date - text

this is the dto definition

[Route("/search/tenders")]
    public class FindTenders : QueryDb<TenderSearchResult>
    {
        public int? PublicationDateWithinLastDays { get; set; }
    }

model:

public class EntitiySearchResult
{
    public DateTime PublicationDate { get; set; }
}

FINAL SOLUTION @mythz solved the registration problem and issue in using interval clause in my original query. The below definition works fine to get records within X days in the past from now. Thanks @mythz

  var autoQuery = new AutoQueryFeature() { MaxLimit = 100 };
            autoQuery.EndsWithConventions.Add("WithinLastDays", new QueryDbFieldAttribute
            {
                Template = "{Field} >= CURRENT_DATE + {Value}::interval",
                ValueFormat = "{0} days ago"
            });

mythz · Accepted Answer · 2018-04-27T13:01:00.037

2

The {Value} is replaced with a db parameter, if you want to change the value of the db parameter you need to use ValueFormat, e.g. ValueFormat="{0} days".

To define ValueFormat format an implicit convention you need to register an EndsWithConventions, e.g:

autoQuery.EndsWithConventions.Add("WithinLastDays", new QueryDbFieldAttribute { 
    Template= "{Field} >= CURRENT_DATE + {Value}::interval", 
    ValueFormat= "{0} days ago" 
});

Also note you likely want CURRENT_DATE + interval not -.

edited Apr 27 '18 at 13:01

answered Apr 25 '18 at 21:33

mythz

141,670
29
246
390

I was trying to use EndsWithConvention and ValueFormat but still the parameter is not passed (I have checked the psql log with executed queries) – marcinn Apr 26 '18 at 20:00
@marcinn It's a db param so the string doesn't need quoting, have you tried `Template = "... interval {Value}", ValueFormat = "{0} days ago"`? – mythz Apr 27 '18 at 01:47
I was also trying to define ValueFormat without quoting but this doesn't work - the statement is generated as `"DATE_FIELD" >= CURRENT_DATE - interval ':0 day'`. The `:0` is not passed – marcinn Apr 27 '18 at 12:10
@marcinn I don't get how, it should look like `interval :0` with the DB param like `2 days ago`. See my previous comment, there should not be any quotes in either your `Template` or `ValueFormat`. – mythz Apr 27 '18 at 12:21
I was trying to define this as `autoQuery.EndsWithConventions.Add("WithinLastDays", new QueryDbFieldAttribute() { Template= "{Field} >= CURRENT_DATE - interval {Value} days ago", ValueFormat= "{0} days ago" });` and it doesn't work :-( – marcinn Apr 27 '18 at 12:25
@marcinn please see my comment it's just `... interval {Value}` the days ago is only in the `ValueFormat` – mythz Apr 27 '18 at 12:27
sorry I had pasted it wrong to my comment. After correction I have PostgresException: 42601: syntax error "$1 `autoQuery.EndsWithConventions.Add("WithinLastDays", new QueryDbFieldAttribute() { Template= "{Field} >= CURRENT_DATE - interval {Value}", ValueFormat= "{0} days ago" });` – marcinn Apr 27 '18 at 12:31
Please update your answer with exactly the code you're using. – mythz Apr 27 '18 at 12:32
@marcinn Also try moving `interval` to `ValueFormat` like `ValueFormat="interval '{0} days ago'"` – mythz Apr 27 '18 at 12:37
@marcinn it also needs casting, see my updated answer. – mythz Apr 27 '18 at 13:01

Servicestack autoquery custom convention doesn't work with PostgreSQL

1 Answers1