当可空列上的JSON_extract函数的值为NULL时，我的where语句得到确认? [英] My where statement with a JSON_extract function on a nullable column is confirmed when said column's value is NULL?

问题描述

我有一个带有JSON_extract方法的where语句. JSON_extract使用名为new_value或old_value的可空列.如果该列包含JSON字符串，则该检查有效，但是当该列为NULL时，将确认where语句.

I have a where statement with a JSON_extract method in it. The JSON_extract uses a nullable column called new_value or old_value. The check works if the column contains a JSON string, but when the column is NULL the where statement gets confirmed.

->where(function($query) use ($other_key, $new_change) {
        $query->where(DB::raw('json_extract(old_value, "$.theme_id") = 1))
            ->orWhere(DB::raw('json_extract(new_value, "$.theme_id") = 1));

当new_value或old_value为NULL时，将返回该行，但NULL的theme_id显然不等于1.有人可以解释这里发生了什么吗?

When the new_value or the old_value is NULL the row gets returned, but the theme_id of NULL obviously isn't equal to 1. Can someone explain what is happening here?

推荐答案

此:

->where(function($query) use ($other_key, $new_change) {
    $query->where(DB::raw('json_extract(old_value, "$.theme_id") = 1))
        ->orWhere(DB::raw('json_extract(new_value, "$.theme_id") = 1));

应该是:

->where(function($query) use ($other_key, $new_change) {
    $query->where(DB::raw("json_extract(old_value, '$.theme_id')"), 1);
        ->orWhere(DB::raw("json_extract(new_value, '$.theme_id')"), 1);

这就是Laravel中where语句的工作方式.我没有插入第二个参数，这就是为什么Laravel假设我正在检查NULL.现在可以了，对我的愚蠢表示歉意.

That's how the where statements in Laravel work. I did not insert a second parameter and that's why Laravel assumed I was checking for NULL. It works now, sorry for my stupidity.

这篇关于当可空列上的JSON_extract函数的值为NULL时，我的where语句得到确认?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！