Hi, How can I filter something based on a related table.
For example, I need to join on a condition.
Parent.id=child.id = This is possible.
But what about extra clause and child.name like “%ABC%”
This is a major setback for us, because we have hundreds of records and currently its retrieving everything based on FK relationships.
I know there is a way to write a custom script. But this is very common, so I was hoping it could be in core DF itself.
Thank you in advance.
@Hussain_Fakhruddin There is a feature request filed to support this which has yet to be implemented. To filter on related data currently a custom script is required.Running something like this as post-process would probably work
var relationship_name = ''; // The name of the relationship we want to filter var related_filter_name = ''; // The name of the URL parameter that contains the related filter var related_service_name = ''; // The name of the service that contains the related data var related_table_name = ''; // The name of the table that contains the related data var related_id_field = ''; // The id field in the related table var related_endpoint = related_service_name + '/_table/' + related_table_name; var options = {}; options.parameters = {}; var params = event.request.parameters; if (params.hasOwnProperty('related')) { if (params.hasOwnProperty(related_filter_name)) { if (params.related.hasOwnProperty(relationship_name)) { options.parameters.fields = related_id_field; options.parameters.filter = params[related_filter_name]; var related_result = platform.api.get(related_endpoint, null, options); var data = related_result.content; if (data.error) throw data.error.message; if (!data.resource) throw 'No records in response'; var ids = data.resource.map(function(record) {return record[related_id_field];}); event.response.content.resource.forEach(function(parent_record){ parent_record[relationship_name] = parent_record[relationship_name].filter(function(obj){ return (ids.indexOf(obj.id) >= 0); }); }); } } }
Hi @mattschaer Thanks for your reply.
Looks like this could work.
However, we have already made enhancement at our end to handle this and multiple sub table query filters from the API itself. Its working fine.
If needed we can submit our enhancement to the main key owners of the open source project as our contribution.
Kind Regards,
Hussain
@Hussain_Fakhruddin you can submit a pull request on GitHub. DreamFactory follows standard Git guidelines for contributions.