CODEWITHSUNDEEP
laravelnotifications
Bishank Badgami
Bishank Badgami

Reputation: 33

Laravel : How to delete Database Notification according to the data present in array of notification

I have followed this link to create database notification in my laravel ecommerce app , when buyer user send question to seller

my application successfully able to create database notification as follows

table : notifications

id | type | notifiable_id | notifiable_type | data | read_at | created_at | updated_at

id = 0b2a7fdf-eea4-4982-a86d-e874bb4f28ef

type = App\Notifications\BuyerQuestionNotification

notifiable_id= 48 

data = {"message":"Someone asked question for Hostelfe","url":"#", "question_id":12024}

read_at = NULL

created_at = 2018-04-07 12:46:42

updated_at = 2018-04-07 12:46:42

now i want to delete database notification row by data->question_id

I have tried below query :

DB::table('notifications')
    ->where('type','App\Notifications\SellerAnswerNotification')
    ->where('data->question_id',2079107489)
    ->first();

got error :

[2019-03-31 13:22:03] local.ERROR: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>'$."question_id"' = ?' at line 1 (SQL: select * from notifications where type = App\Notifications\SellerAnswerNotification and data->'$."question_id"' = 2079107489)

I have also tried below query:

$result=DB::table('notifications')->whereRaw("JSON_EXTRACT(data, '$.question_id') = ?", [2079107489]);

But got similar error

How to correct the query to get row and delete row by json in notifications table?

Upvotes: 2

Views: 854

Answers (2)

Koussay
Koussay

Reputation: 183

Here you go, I tried this one and it worked:

DB::table('notifications')
->where('type','App\Notifications\BuyerQuestionNotification')
->where('data->question_id',12024)
->first();

NOTE: it will work only for MySQL 5.7.13 and later. For further reading : https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#operator_json-inline-path https://laravel.com/docs/5.8/queries#json-where-clauses

Upvotes: 1

Bryan Lopez
Bryan Lopez

Reputation: 16

When you do json_decode($data) then try where($data['question_id'])

Upvotes: 0

Related Questions