Laravel:如何通过data-> postid删除数据库通知行 [英] Laravel : How to delete Database Notification row by data->postid

问题描述

当一个用户喜欢另一个用户的帖子时，我已经通过此链接在我的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

表格:通知

id |类型notifiable_id | notifiable_type |数据read_at | created_at | Updated_at

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

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

type = App\Notifications\PostLiked

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

现在我要按data-> postid删除数据库通知行

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

我已经尝试过以下查询:

I have tried below query :

DB::table('notifications')
    ->where('type','App\Notifications\PostLiked')
    ->where('data->postid',17)
    ->first();

得到错误:

QueryException SQLSTATE [42000]:语法错误或访问冲突:1064 您的SQL语法有误；检查手册 对应于您的MariaDB服务器版本，以使用正确的语法 第1行('SQL:select * from 通知，其中(类型= App \ Notifications \ PostLiked 和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 = App\Notifications\PostLiked 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();

这篇关于Laravel:如何通过data-> postid删除数据库通知行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！