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
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.
@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.
@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
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.
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 <= 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
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.
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.
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’,
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