To accomplish the task of querying a JSON string inside of an Eloquent relationship, you can use the whereJsonContains method provided by Laravel. This method allows you to query JSON column data types to find records where the JSON column contains the specified value.
Here's how you can modify your existing query to include the check for the "EAM" string inside the "sms_options" key:
$usersToNotify = User::whereHas('preferences', function ($query) {
$query->where('key', 'sms_preference')->where('value', '1');
})
->whereHas('preferences', function ($query) {
$query->where('key', 'sms_options')->whereJsonContains('value', 'EAM');
})
->whereHas('userAlerts', function ($query) use ($incidentType) {
$query->where('notifier_id', $incidentType);
$query->whereColumn('user_alerts.duty_status', 'users.duty_status');
})
->get();
In this query, we've added another whereHas clause to filter the users based on their preferences. The whereJsonContains method is used to check if the 'value' column (which contains JSON data) has an element with the value "EAM".
Please note that the whereJsonContains method is only available for MySQL 5.7+ and PostgreSQL 9.4+, and the column type must be a valid JSON column type in your database.
Also, ensure that the 'preferences' relationship is correctly defined in your User model to reference the user_preferences table. Here's an example of what the relationship might look like:
public function preferences()
{
return $this->hasMany(UserPreference::class);
}
Replace UserPreference with the actual name of your Eloquent model for the user_preferences table.