Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Hello
I hope my title doesn't confused you :D
I have a table called "Notifications" which I add my custom notifications in it for specific roles
this is the "Messages" tabel strcuture
id | roles | message
1 | ["superadmin","trial"] | test message
now I want to only users who has "superadmin" role or "trial" role can see this message.
this is my "User" table strcuture
id | roles | name
1 | ["superadmin"] | reysa
2 | ["admin","moderator"] | user
3 | ["trial"] | user 2
how can I do this in eloquent with a where() function? is it possible or I have to go get all the data and then filter them one by one with foreach based on user roles.
I have many roles (almost 11) that's why I want to filter the data with where() while I'm calling the eloquent
thanks in advanced
Use whereJsonContains
https://laravel.com/docs/9.x/queries#json-where-clauses
User::whereJsonContains('roles', $message->roles)->get()
However, I recommend in the future to use one table for roles and use many-to-many relationship instead. Searching JSON text can be expensive.
@MohamedTammam no no you both got it wrong I think :D
I don't want to get "Users" where they have "superadmin" , I want to get data from first time (Messages) where user has a role that message has
based on the example tables , both user id 1 and 3 should be able to see the message id 1 because they have "superadmin" and "trial" in their roles
I've already tried wherJsonContains but it returns null , because both $message->roles and $user->roles are json array
another thing is my user might have a role that it's not in messages->roles
@Reysa Convert it to an array before putting in the condition
The conversion should be done on a getter but I will do it here to demonstrate.
Message::whereJsonContains('roles', json_decode($user->roles))->get()
https://laravel.com/docs/9.x/eloquent-mutators#array-and-json-casting
@MohamedTammam still return null
because my user roles is like this:
["superadmin","admin","booster","moderator"]
but the message roles is this
["superadmin"]
so it's not equal and returns null
but what I want is , if my user has the one of the roles in message table , I get that data
@Reysa Try
$query = Message::query()
foreach(json_decode($user->roles) as $role)
$query->orWhereJsonContains('roles', $role)
$messages = $query->get();
@MohamedTammam that works perfectly!! thanks man <3
I did that before but the problem was I used whereJsonContians instead of ORwhereJsonContains
sometimes I hate my job :D
@Reysa Sometimes we all do :)
$users = User::whereJsonContains('roles', ['superadmin', 'trial'])->get();
Please or to participate in this conversation.