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
toeknee17mo ago
Did you resolve this as I see it is ticked resolved?
Falk Maria Zeitsprung
no sorry, its NOT resolved. i just edited it.
Iliyas M
Iliyas M17mo ago
Can u elaborate ur question more? I am not able to understand what u r looking for.
Falk Maria Zeitsprung
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
toeknee17mo 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
Iliyas M17mo 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
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
Patrick Boivin17mo ago
Try $state instead of $value
Falk Maria Zeitsprung
@pboivin error is gone! thanks 🙂 yesss
Falk Maria Zeitsprung
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
Patrick Boivin17mo ago
In this example you want all applicants with a {"language":"german", ...} entry?
Falk Maria Zeitsprung
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
Patrick Boivin17mo ago
Are you using mysql?
Falk Maria Zeitsprung
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
Patrick Boivin17mo ago
Ok, different issues 😅 What's your mysql version?
Falk Maria Zeitsprung
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
Patrick Boivin17mo ago
I mean, yeah, that's not a bad solution if the JSON is always formatted without any whitespaces
Falk Maria Zeitsprung
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
Patrick Boivin17mo 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
Hey @pboivin great. Thanks very much! :)_ you are completely right 🙂 haha. i had to do several intents.
Want results from more Discord servers?
Add your server