Open timwis opened 6 years ago
/cc @awm33
Just wanted to confirm that CKAN's Datastore API supports SQL.
And you can issue "real" SQL, not just a filtering dialect limited to one dataset at a time - aggregations, joins, spatial queries if you have PostGIS installed, etc.
And with the new Resource Queries (https://github.com/ckan/ckan/pull/3816), you can now even create "Stored Queries." This is most useful for expensive queries, or to slice large datasets (e.g. from master 311 file, derive other dataset resources sliced by year, neighborhood, etc.)
Rather than trying to stuff it into rarely-used config options like
aggregateFunction
andvalueField
, we could just allow an "advanced" option/escape hatch, where you literally pass SQL. This would of course have to be limited to the Carto provider as services like Socrata, CKAN, DKAN don't support SQL queries (as far as I know).The tricky part is injecting the global filters into the user's SQL string. A couple options so far:
Pass the filter object, with a method to convert it to a WHERE clause, to the SQL configuration "callback", e.g.:
Use the user's SQL as a nested query (
select * from (select from ...) where <global filters>
)The 2nd option probably makes more sense if we only need it for carto, but the 1st option is more likely to fit into other providers.