Description
I've been quite occupied recently with many projects, one of them is obviously a new website using BOLT4.
There still is an issue with the PostgreSQL implementation from #1933.
While templating and setting the content to order on a custom field, a famous JSONB error is thrown.
Select some records using TWIG:
{% setcontent records = ('entries') where { groups: 'expertises' } limit 4 orderby 'sequentie' %}
The error is thrown directly upon trying to load the page:
SQLSTATE[42883]: Undefined function: 7 ERROR: function length(jsonb) does not exist
LINE 1: ...= $4 ORDER BY CAST(SUBSTRING(b4_.value FROM 3 FOR LENGTH(b4_...
This is because the field is encoded in JSON, and need to be CAST to string first WITHIN the length.
So the query should become something like: ORDER BY CAST(SUBSTRING(b4_.value FROM 3 FOR LENGTH(CAST b4_.. AS TEXT)...
As this issue is getting overly complicated it might be better to rethink about the implementation how content queries are handled, or built in a robust check to test if a field is JSON(B), and if needed in-place CAST it as TEXT.
I've temporarily overcome this issue by sorting within TWIG. This solves my short-term needs. Obviously for the long term this needs a proper fix. I'll see what I can do myself in some spare time as well.. :)
Temporary bypass:
{% setcontent records = ('entries') where { groups: 'expertises' } limit 4 %}
{% for record in records|sort((a, b) => a.sequentie <=> b.sequentie) %}
Details
| Question | Answer |
|---|---|
| Relevant Bolt Version | 4.1 |
| Install type | Composer install |
| BC Break | no |
| PHP version | 7.2 |
| Web server | Apache, version 2.4.46 |
| For UX/UI issues | N/A |
Reproduction
Bug summary
Using PostgreSQL as database causes query break when setting an ORDERBY on custom field.
Specifics
Steps to reproduce
- Install BOLT the usual way, using PostgreSQL as backend
- add a number field to the ContentType of 'Entries', to be ordered on.
- Use the as-above-described TWIG code to retrieve some records
Expected result
An ordered set of results nicely embedded in an array.