selectfilter from json data

I have a database filed where i have saved something like this:

[{"language":"german","language_level":"Native language"},{"language":"english","language_level":"Fluent \/ Fluent business"},{"language":"spanish","language_level":"Fluent \/ Fluent business"}]

[{"language":"german","language_level":"Native language"},{"language":"english","language_level":"Fluent \/ Fluent business"},{"language":"spanish","language_level":"Fluent \/ Fluent business"}]

Cane be one o n lines. Now i need a SelectFilter where i can choose a language. And language_level (or language_level) withou selection. I try:

SelectFilter::make('languages')
    ->options(['english' => 'Englisch', 'spanish' => 'Spanisch', 'french' => 'Französisch'])
    ->query(fn(Builder $query, $value): Builder => $query->whereJsonContains('languages')),

SelectFilter::make('languages')
    ->options(['english' => 'Englisch', 'spanish' => 'Spanisch', 'french' => 'Französisch'])
    ->query(fn(Builder $query, $value): Builder => $query->whereJsonContains('languages')),

` but get error: Unable to resolve dependency [Parameter #1 [ <required> $value ]] in class App\Filament\Resources\ApplicantResource

Solution:

Try $state instead of $value

Jump to solution

20 Replies

toeknee•12mo ago

Did you resolve this as I see it is ticked resolved?

Falk Maria Zeitsprung•12mo ago

no sorry, its NOT resolved. i just edited it.

Iliyas M•12mo ago

Can u elaborate ur question more? I am not able to understand what u r looking for.

Falk Maria Zeitsprung•12mo ago

Sure, sorry: I have a database field 'languages', where i have saved on or several json strings, like the above ones. they contain the languae, an applicant speaks, and also the level. It can be 1 to n lines. But at least one. No i want to create in my ApplicantsResource in the table view a Filter, where i can select a language. F.E. french, so that the list will show only the applicants which have french in their language field. I am struggling with the json string. I dont get the language out of the stron to compare it with teh selected language in the dropdown list. I thougt i can do it with somehtin like

$query->whereJsonContains('languages', ['language' => $value]);

$query->whereJsonContains('languages', ['language' => $value]);

` but i cant get it to work.

toeknee•12mo ago

Yeah that's a bit of a tricky scenario, most people would say them as individual database values indexed of the language

Iliyas M•12mo ago

Try this one

$query->whereJsonContains('languages->language',$value)

$query->whereJsonContains('languages->language',$value)

Or this one,

$query->whereJsonContains('languages',[['language' => $value]])

$query->whereJsonContains('languages',[['language' => $value]])

Falk Maria Zeitsprung•12mo ago

if i do:

SelectFilter::make('languages')
                    ->options(['english' => 'Englisch', 'spanish' => 'Spanisch', 'french' => 'Französisch'])
                    ->query(fn(Builder $query, $value): Builder => $query->whereJsonContains('languages',[['language' => $value]])),

SelectFilter::make('languages')
                    ->options(['english' => 'Englisch', 'spanish' => 'Spanisch', 'french' => 'Französisch'])
                    ->query(fn(Builder $query, $value): Builder => $query->whereJsonContains('languages',[['language' => $value]])),

` i get the error:

Unable to resolve dependency [Parameter #1 [ <required> $value ]] in class App\Filament\Resources\ApplicantResource

Unable to resolve dependency [Parameter #1 [ <required> $value ]] in class App\Filament\Resources\ApplicantResource

` 😦

Solution

Patrick Boivin•12mo ago

Try $state instead of $value

Falk Maria Zeitsprung•12mo ago

@pboivin error is gone! thanks 🙂 yesss

Falk Maria Zeitsprung•12mo ago

But i have still the problem, that the filter is not working: My table field languages can look like this:

[{"language":"albanian","language_level":"Conversation"}]

[{"language":"albanian","language_level":"Conversation"}]

` or like this

[{"language":"english","language_level":"Fluent \/ Fluent business"},{"language":"russian","language_level":"Lengua materna"},{"language":"hungarian","language_level":"Conversation"},{"language":"spanish","language_level":"Conversation"}]

[{"language":"english","language_level":"Fluent \/ Fluent business"},{"language":"russian","language_level":"Lengua materna"},{"language":"hungarian","language_level":"Conversation"},{"language":"spanish","language_level":"Conversation"}]

` 1-n languages and language-levels. I am using:

SelectFilter::make('languages')
                    ->options(AppHelper::selectTranslationArray('languagesWorld'))
                    ->query(fn(Builder $query, $state): Builder => $query->whereJsonContains('languages',[['language' => $state]])),

SelectFilter::make('languages')
                    ->options(AppHelper::selectTranslationArray('languagesWorld'))
                    ->query(fn(Builder $query, $state): Builder => $query->whereJsonContains('languages',[['language' => $state]])),

` The table list is emptry from the beginning. And when i select a language, it still keeps empty. No error message.

Patrick Boivin•12mo ago

In this example you want all applicants with a {"language":"german", ...} entry?

Falk Maria Zeitsprung•12mo ago

At the end i resolved it this way:

SelectFilter::make('languages')
                    ->options(AppHelper::selectTranslationArray('languagesWorld'))
                    ->query(function (Builder $query, $state) {
                        $selectedLanguage = $state['value'] ?? null; // Get the selected language

                        if ($selectedLanguage) {
                            $json = json_encode(['language' => $selectedLanguage]);
                            $query->whereRaw("JSON_CONTAINS(languages, ?)", [$json]);
                        }

                        return $query;
                    }),

SelectFilter::make('languages')
                    ->options(AppHelper::selectTranslationArray('languagesWorld'))
                    ->query(function (Builder $query, $state) {
                        $selectedLanguage = $state['value'] ?? null; // Get the selected language

                        if ($selectedLanguage) {
                            $json = json_encode(['language' => $selectedLanguage]);
                            $query->whereRaw("JSON_CONTAINS(languages, ?)", [$json]);
                        }

                        return $query;
                    }),

` Thanks for all your help!! yes @pboivin

Patrick Boivin•12mo ago

Are you using mysql?

Falk Maria Zeitsprung•12mo ago

yes and mariadb on production On production with mariadb i get now the error SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION humres.JSON_CONTAINS does not exist

Patrick Boivin•12mo ago

Ok, different issues 😅 What's your mysql version?

Falk Maria Zeitsprung•12mo ago

mysql Ver 8.0.29 for macos12 on x86_64 (MySQL Community Server - GPL) i solved it now for my mac and the production server this way:

SelectFilter::make('languages')
                    ->options(AppHelper::selectTranslationArray('languagesWorld'))
                    ->query(function (Builder $query, $state) {
                        if (isset($state['value'])) {
                            $searchTerm = '%"language":"' . $state['value'] . '"%';
                            return $query->where('languages', 'LIKE', $searchTerm);
                        } else {
                            return $query;
                        }
                    }),

SelectFilter::make('languages')
                    ->options(AppHelper::selectTranslationArray('languagesWorld'))
                    ->query(function (Builder $query, $state) {
                        if (isset($state['value'])) {
                            $searchTerm = '%"language":"' . $state['value'] . '"%';
                            return $query->where('languages', 'LIKE', $searchTerm);
                        } else {
                            return $query;
                        }
                    }),

Patrick Boivin•12mo ago

I mean, yeah, that's not a bad solution if the JSON is always formatted without any whitespaces

Falk Maria Zeitsprung•12mo ago

for any more elegant solution i would be very thankfull. But is has to work on maria db. yes, thats the case. (not sure about which version of MariaDB would be needed....) ChatGPT4 says: In this example, we're using MySQL's JSON_CONTAINS function which returns 1 if a JSON document contains a specified value. Note that this will only work if you're using MySQL 5.7.8 or higher or MariaDB 10.2.3 or higher. This is because the JSON_CONTAINS function was added in these versions.

Patrick Boivin•12mo ago

I would double-check that but it's probably right 🙂 Ok I'm not super experienced with JSON columns... I did a quick test just out of curiosity and this seems to work:

    return $query->where(DB::raw('JSON_EXTRACT(languages, "$[*].language")'), 'LIKE', '%german%');

    return $query->where(DB::raw('JSON_EXTRACT(languages, "$[*].language")'), 'LIKE', '%german%');

It's not that much better than your solution above, but it'll search only in the combined values of languages.*.language instead of the entire column.

Falk Maria Zeitsprung•12mo ago

Hey @pboivin great. Thanks very much! :)_ you are completely right 🙂 haha. i had to do several intents.

Gaming

Programming

selectfilter from json data