Laravel:如何按数据删除数据库通知行->postid

2022-01-15 00:00:00 mariadb php laravel

当一个用户喜欢其他用户的帖子时，我已按照此链接在我的 laravel 博客应用中创建数据库通知

I have followed this link to create database notification in my laravel blog app , when one user likes other user's post

https://laravel.com/docs/5.5/notifications#database-notifications

我的应用程序成功创建数据库通知如下

my application successfully able to create database notification as follow

表格:通知

标识 |类型 |notifiable_id |通知类型 |数据 |read_at |created_at |更新时间

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

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

type = AppNotificationsPostLiked

notifiable_id= 48 

data = { "event":"LIKE",
         "postid":17,
         "sender":{
                   "id":50,
                   "name":"Developer",
                  }
       }

read_at = NULL

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

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

现在我想通过数据删除数据库通知行->postid

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

我尝试过以下查询:

DB::table('notifications')
    ->where('type','AppNotificationsPostLiked')
    ->where('data->postid',17)
    ->first();

出错了:

QueryException SQLSTATE[42000]:语法错误或访问冲突:1064您的 SQL 语法有错误；检查手册对应于您的 MariaDB 服务器版本，以便使用正确的语法'>'$."postid"' = 17) 在第 1 行限制 1' (SQL: select * from通知位置(类型 = AppNotificationsPostLiked和 data->'$."postid"' = 17) 限制 1)

QueryException 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 '>'$."postid"' = 17) limit 1' at line 1 (SQL: select * from notifications where (type = AppNotificationsPostLiked and data->'$."postid"' = 17) limit 1)

我也尝试过以下查询:

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

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

{
connection: { },
grammar: { },
processor: { },
bindings: {
select: [ ],
join: [ ],
where: [
17
],
having: [ ],
order: [ ],
union: [ ]
},
aggregate: null,
columns: null,
distinct: false,
from: "notifications",
joins: null,
wheres: [
{
type: "raw",
sql: "JSON_EXTRACT(`data`, '$.postid') = ?",
boolean: "and"
}
],
groups: null,
havings: null,
orders: null,
limit: null,
offset: null,
unions: null,
unionLimit: null,
unionOffset: null,
unionOrders: null,
lock: null,
operators: [
"=",
"<",
">",
"<=",
">=",
"<>",
"!=",
"<=>",
"like",
"like binary",
"not like",
"between",
"ilike",
"&",
"|",
"^",
"<<",
">>",
"rlike",
"regexp",
"not regexp",
"~",
"~*",
"!~",
"!~*",
"similar to",
"not similar to",
"not ilike",
"~~*",
"!~~*"
],
useWritePdo: false
}

请建议我正确查询以在通知表中按 json (data.key) 获取行和删除行

please suggest me correct query to get row and delete row by json (data.key) in notifications table

推荐答案

它对我有用.你可以使用这个查询:

It worked for me. You can use this query: 

->where('data', 'like', '%"postid":17%' )->first();

相关文章