4 ms·
Heh, this is the reason Takahe is currently broken on SQLite. Hashtags currently need a JSON contains.
by TkTech 4y ago
Heh, this is the reason Takahe is currently broken on SQLite. Hashtags currently need a JSON contains.
- simonw 4y agoIt's possible to simulate JSON contains in SQLite using a devious subselect. Here's an example: https://datasette.io/content/plugin_repos?_facet_array=tags&tags__arraycontains=Data+Import https://datasette.io/content/plugin_repos?_facet_array=tags&... That page shows every row where the tags column (a SQLite JSON list of strings) contains the string "Data Import" Here's the underlying SQL: https://datasette.io/content?sql=select%0D%0A++rowid%2C%0D%0A++repo%2C%0D%0A++tags%0D%0Afrom%0D%0A++plugin_repos%0D%0Awhere%0D%0A++%3Atag+in+%28%0D%0A++++select%0D%0A++++++value%0D%0A++++from%0D%0A++++++json_each%28%5Bplugin_repos%5D.%5Btags%5D%29%0D%0A++%29%0D%0Aorder+by%0D%0A++rowid%0D%0Alimit%0D%0A++101&tag=Data+Import https://datasette.io/content?sql=select%0D%0A++rowid%2C%0D%0... select rowid, repo, tags from plugin_repos where :tag in ( select value from json_each(plugin_repos.tags) ) order by rowid limit 101 I haven't tried it yet, but I have a strong hunch that this could be dramatically accelerated using a join against a FTS table on that "tags" column in order to first filter the rows down to a likely subset.
- rcarmo 4y agoDjango would need to be patched to do that, since the default SQLite provider would make it hard to do that cleanly. Here’s the relevant Takahe issue: https://github.com/jointakahe/takahe/issues/325 https://github.com/jointakahe/takahe/issues/325
- rcarmo 4y agoYep. That’s exactly what I stumbled upon myself.