Hello,

I have a model that have a $jsonable element to manage a billing address :

{
    firstName: 'John'
    name: 'Doe'
    (...)
}

The different attributes are on different columns like that :

'billing[firstName]':
    label: Firstname
    type: text
    searchable: true
    (...)

Now I want to add a filter of "text' type to search on my firstname column :

scopes:
    name:
        label: Firstname
        type: text
        conditions: billing[firstName] = :value

When I try to use my filter, it display the following error :

"SQLSTATE[HY000]: General error: 1 near "[firstName]": syntax error (SQL: select count(*) as aggregate from "mymodule_shop_customers" where billing[firstName] = 'John')" on line 664 of /vendor/laravel/framework/src/Illuminate/Database/Connection.php

I understand why it doesn't work and I don't have billing[firstName] column in my database table (SQLite database to be precise), I have a billing column with firstName attribute.

Let me quote a sentence on your homepage :

Easy to learn : Using October doesn't have a steep learning curve and grows with you. It is intuitive and self-explanatory.

The array syntax (billing[firstName]) when you deal with $jsonable perfectly works in fields.yaml and columns.yaml. It should work the same way in config_filters.yaml to keep things "intuitive and self-explanatory", that's why I think it's the role of October CMS system to add filter support in $jsonable elements.

I precise that I work with SQLite, that doesn't have JSON column type (it's saved as text), so I don't think that october should rely on dedicated json search function that are only available with MySQL. Instead, it should get the appropriate column (before brackets), get the value as text, convert as json, and then search in the attribute (in brackets) for results.

Best regards,

Alex

Last updated 6 years ago