fix: correct mongo-knex regexp resolution with relations

This solves issue with relation regexp filtering. http://localhost:2368/ghost/api/v4/content/posts/?key=redacted&filter=tag:~'foo'

{"errors":[{"message":"Internal server error, cannot list posts.","context":"select count(distinct posts.id) as aggregate from `posts` where (`posts`.`status` = 'published' and (`posts`.`type` = 'post' and `posts`.`id` in (select `posts_tags`.`post_id` from `posts_tags` inner join `tags` on `tags`.`id` = `posts_tags`.`tag_id` where `tags`.`slug` like {}))) - 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 ')))' at line 1","type":"InternalServerError","details":null,"property":null,"help":null,"code":"ER_PARSE_ERROR","id":"redacted","ghostErrorCode":null}]}

I thought of using Knex Like and ILike but for now it won't work because of COLLATION 'utf8_bin'. COLLATION 'utf8_bin' is not valid for CHARACTER SET 'latin1'

The behavior aligns with commit 7b8798a6fa7870c98648cae5a494eb761638a208. MySQL case-sensitive LIKEs also won't work without casting to BINARY. For an actual database agnostic solution, we need to rework everything around this.

I tried some SQL Injection test cases but NQL parser works well :laughing:

TryGhost / NQL

fix: correct mongo-knex regexp resolution with relations #47