Server-side filter returns SQL error when using "contains" or "starts with" clauses

Hi!

I’m trying to configure a server side filter in a Role but it only works for some operations (namely =, !=, <, >, <=, >=, is null, is not null). If I select any other operations (i.e. in, not in, starts with, ends with, contains) I get a SQL error much like this:

Failed to retrieve records from 'userstatus'.\nSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'contains 'Online' LIMIT 1000' at line 1

In the Role > Access configurations I defined an advanced filter with the following values:
Field: status. Operator: any of the aforementioned. Value: {desiredstatus}.

In the Role > Lookup keys I configured the following:
Key: desiredstatus. Value: Online

Am I doing something wrong, or just found a bug?

Thanks!

Elivs

@Elvis_Fernandes

I have a similar problem with MySQL using the “contains” or “starts with” clauses. Did you find a workaround? Is is a bug?

Thanks,

Tunde

@tunde

To me it looks like a bug, and I didn’t find a workaround yet. Currently I’m installing DF in my local machine to try to debug the code and possibly find the error - or to understand what I’m doing wrong.

I think that one workaround would be to use server side scripts to filter data - but probably the performance would be impacted.

Elvis

@tunde in your actual sql filters, for start with and contain you should be using LIKE and wildcards (%). See documentation here: Querying Records with Filters

@Elvis_Fernandes I have confirmed the behavior you’re seeing.
Filing a bug report now.

1 Like

@formerstaff Thanks.
Can you confirm if lookups can be used as input parameters for stored procedures?

@Elvis_Fernandes
Can you share the work around experience you have with server side scripts to filter data on a database table (not stored procedure)? I am guessing that I have to read the lookup value for the role and pass that into the filter. Any help will be greatly appreciated.

Tunde

@tunde
Actually I didn’t use server side scripts to filter data, I just thought of a temporary solution for this problem. I understand that using server side scripts to filter data maybe a wrong use of Dreamfactory features. I’d sugest you to try the solution pointed by @formerstaff.

But if you’re feeling adventurous, you can try something like the “Modify query results after database GET” example in this link: https://github.com/dreamfactorysoftware/dsp-core/wiki/Server-Side-Scripting.

@formerstaff

I tried what you recommended with DF 2.0.4 and was not successful. Incidentally, I used to get this working with filters with DF 2.0.2 but noticed that now the params sent as date for filtering is creating problems.

{“error”:{“context”:null,“message”:“Failed to retrieve records from ‘ch_notifications’.\nDateTime::__construct(): Failed to parse time string (2016-01-15 and ins_ts &lt;= 2016-01-15 and de41 like %2070%) at position 11 (a): The timezone could not be found in the database”,“code”:500,“trace”:[“0 /opt/bitnami/apps/dreamfactory/htdocs/vendor/dreamfactory/df-core/src/Resources/BaseDbTableResource.php(486): DreamFactory\Core\SqlDb\Resources\Table->retrieveRecordsByFilter(‘ch_notification…’, ‘ins_ts >= 2016…’, Array, Array)”,“1 [internal function]: DreamFactory\Core\Resources\BaseDbTableResource->handleGet()”,“2 /opt/bitnami/apps/dreamfactory/htdocs/vendor/dreamfactory/df-core/src/Components/RestHandler.php(271): call_user_func(Array)”,“3 /opt/bitnami/apps/dreamfactory/htdocs/vendor/dreamfactory/df-core/src/Components/RestHandler.php(177): DreamFactory\Core\Components\RestHandler->processRequest()”,“4 /opt/bitnami/apps/dreamfactory/htdocs/vendor/dreamfactory/df-core/src/Components/RestHandler.php(224): DreamFactory\Core\Components\RestHandler->handleRequest(Object(DreamFactory\Core\Utility\ServiceRequest), ‘ch_notification…’)”,“5 /opt/bitnami/apps/dreamfactory/htdocs/vendor/dreamfactory/df-core/src/Services/BaseDbService.php(116): DreamFactory\Core\Components\RestHandler->handleResource(Array)”,"6

Can you provide the original query that generated this error? It will help troubleshoot and/or file a bug report.

@formerstaff

Thanks. Kindly find below.

[2016-01-14 19:42:40] local.INFO: * Script “POSVASDB._table.ch_notifications.get.pre_process” output:
object(Object)#1175175130 (5) {
[“filter”] =>
string(46) “ins_ts >= 2016-01-01 and ins_ts <= 2016-01-13”
[“include_count”] =>
string(4) “true”
[“limit”] =>
string(2) “10”
[“offset”] =>
string(1) “0”
[“order”] =>
string(11) “ins_ts DESC”
}

Try this instead: string(46) “(ins_ts >= 2016-01-01) and (ins_ts <= 2016-01-13)”

@formerstaff

Thanks a lot, that fix it. Great platform! Looking forward to getting lookup keys defined for roles working for tables and stored procedures for use with Server Side Filters in the next release.

Tunde

@Elvis_Fernandes @formerstaff

Folks, I still need help !!!

I have the following in my script checking for the filter and then appending the custom filter on a pre_process
event.request.parameters.filter += ’ AND (de41 like ’ + obj.value + ‘)’;

It does not get applied to the query; this is my workaround for the lookup_key I am developing.

So far I can get the lookup_key associated with the role i.e. obj.value.

Thanks

There’s a bug where the event script changes the request but it doesn’t stick. We’re trying to get this fixed for the next release next week.

1 Like

@toddappleton

Is the release addressing this fix out yet?

Thanks

Yes. This fix was included in the 2.1 release from this past weekend.

@formerstaff

I just installed and tested on 2.1.1 . looks like the issue is still there …

[Fri Feb 05 23:27:53.869688 2016] [proxy_fcgi:error] [pid 7051:tid 139998334473984] [client 72.83.0.139:48275] AH01071: Got error ‘PHP message: REST Exception #500 > Failed to retrieve records from ‘ch_notifications’.\nSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘starts with ‘2070’) ORDER BY ins_ts DESC LIMIT 10’ at line 1\n’,

Note the 'start with …

I’ll have a look. Thanks.

Hi,

How can I use IN clause in here. I Mentioned my query like this one

SELECT * FROM (post) WHERE post.user_id IN (SELECT follow.following_id from follow WHERE follow.user_id=’".$user_id."’ ) OR post.user_id = ‘".$user_id."’ AND post.private_post = 0 ORDER BY post.created desc