Level 75
Have you tried with query builder. Also search some post from @tray2 where he discusses not storing json in a field.
I generally agree, unless it's a very short simple json, nothing nested.
Jan 13, 2023
7
Level 1
Search with whereJsonContains in Eloquant
Hello Laracasts forum, I have a column in MySQL database that is storing multiple IDs that crosspond to different records in another table. What I have tried to do is instead of making a pivot table(if that terminology is right) I have stored all the IDs in a column and cast it into an array in Model.
This is what is being stored in the MySQL column. and Column name is belongs_to_sport = ["1", "2", "3"]
Now I want to query in Eloquent to filter these records such as
$News = News::whereJsonContains('belongs_to_sport, 1)->get(); This Query should give me the above record.
$News = News::whereJsonContains('belongs_to_sport, 5)->get(); This Query should not give me the above record.
Does that make sense? Is that even possible what I am trying to achieve here? What I am doing wrong?
Level 1
@jlrdw Yes, it's a very simple JSON (nothing nested).
Level 75
@nomi just tested a column:
$user = DB::table('mylikes')
->whereJsonContains('likes', 'biking')
->get();
dd($user);
Result:
Illuminate\Support\Collection {#442 ▼ // app\Http\Controllers\ArrayController.php:54
#items: array:1 [▼
0 => {#444 ▼
+"id": 2
+"name": "Rob"
+"likes": "["biking", "jogging", null, "baseball"]"
}
]
#escapeWhenCastingToString: false
Works.
Level 1
@jlrdw Thank you for your response but it still does not work in my case.
My Query $news = DB::table('news')->whereJsonContains('n_belongs_to_Sports', 1)->get(); dd($news);
The response that I get
Illuminate\Support\Collection {#1282 ▼ // app\Http\Controllers\SportController.php:66 #items: [] #escapeWhenCastingToString: false }
One of the rows in the MYSQL table does contain this data n_belongs_to_Sports = ["1", "2"]
What I am doing wrong? Does it have to do with the MYSQL version or i am casting this column as an array in Model?
Level 1
I got it to work. I was not adding the " " around the variable that is being passed to the query.
Old Query (Not working) $news = DB::table('news')->whereJsonContains('n_belongs_to_Sports', $sport_cateory_id)->get(); dd($news);
New Query(Working) $news = DB::table('news')->whereJsonContains('n_belongs_to_Sports', "$sport_cateory_id")->get(); dd($news);
Difference $sport_cateory_id needs to be "$sport_cateory_id"
Thank you for your Help. Much appreciate it @jlrdw
Level 75
@nomi I did mention to use query builder:
DB::table('news')
Level 1
@jlrdw Yes, you did mention that. Thanks
Please or to participate in this conversation.