Weekly update – 9 August 2024

[ryan]

This week I've bumped the dev branch version to 3.0.241. Relative to the previous version, this one has 29 commits with a mixture of issue resolutions, new features and improvements, and other minor updates. A couple of PRs were also added today as well. 

This week I've also continued work on the FieldtypeCustom module that I mentioned last week. There were some questions about its storage model and whether you could query its properties from $pages->find() selectors (the answer is yes). Since the properties in a custom field are not fixed, and can change according to your own code and runtime logic, it doesn't map to a traditional DB table-and-column structure. That's not ideal when it comes to query-ability. But thankfully MySQL (5.7.8 and newer) supports a JSON column type and has the ability to match properties in JSON columns in a manner similar to how it can match them in traditional DB columns. Though the actual MySQL syntax to do it is a little cryptic, but thankfully we have ProcessWire selectors to make it simple. (It works the same as querying any other kind of field with subfields). MySQL can also support this with JSON encoded in a more traditional TEXT column with some reduced efficiency, though with the added benefit of supporting a FULLTEXT index. (Whereas the JSON column type does not support that type of index). For this reason, FieldtypeCustom supports both JSON and TEXT/MEDIUMTEXT/LONGTEXT column types. So you can choose whether you want to maximize the efficiency of column-level queries, or add the ability to perform text matching on all columns at once with a fulltext index. While I'm certain it's not as efficient as having separate columns in a table, I have been successfully using the same solution in the last few versions of FormBuilder (entries), and have found it works quite well. More soon. Thanks for reading and have a great weekend!

[Aleksey Popov]

Thank You!

[BitPoet]

On 8/9/2024 at 11:39 PM, ryan said:

MySQL can also support this with JSON encoded in a more traditional TEXT column with some reduced efficiency, though with the added benefit of supporting a FULLTEXT index. (Whereas the JSON column type does not support that type of index). For this reason, FieldtypeCustom supports both JSON and TEXT/MEDIUMTEXT/LONGTEXT column types. So you can choose whether you want to maximize the efficiency of column-level queries, or add the ability to perform text matching on all columns at once with a fulltext index.

Just a thought for future in case performance really becomes an issue for someone: it's possible to add a generated + stored column to a MySQL table that feeds itself from a dedicated value in a JSON column, and this column can of course have a fulltext index. A hook on PageFinder::getQueryUnknownField could then "redirect" the query to the generated column. Would only work on single valued fields, of course.

For anybody interested in creating such a generated field from a JSON field, here's a blog entry that explains it better than I could.