질문을 삭제하지 말아주세요.!
 
1
0
-1

php version : 7.x

laravel version : 5.3

문제 :

데이터 베이스에 다음과 같이 db_content라는 column에 json data가 있습니다.

{"\uc774\ub984":"\uae40\ud604\uc2b9","\uc5f0\ub77d\ucc98":"01089878824","\uc0c1\ub2f4\ub0b4\uc6a9":"\ub300\ud658\ub300\ucd9c"}

{"\uc774\ub984":"\ucd5c\uac11\ub9bc","\uc5f0\ub77d\ucc98":"01041719560","\uc0c1\ub2f4\ub0b4\uc6a9":"\ub300\ud658\ud558\uace0\uc2f6\uc2b5\ub2c8\ub2e4 \ub300\ucd9c\uc804\ud654\uac00\ub9ce\uc544\uc11c\ubb38\uc790\uc8fc\uc2dc\uace0 \uc804\ud654\uc8fc\uc138\uc694"}


json의 key값은 처음부터 "이름", "연락처", "상담내용"입니다.

이 key값들을 이용해서 해당하는 value값을 갖고 있는 row를 query할려고 합니다.

문제는 여기서부터 입니다.

"이름"이라는 key값은 where로 query가 매우 잘 됩니다.

그런데 "연락처"라는 key값은 이상하게 query가 되질 않습니다...

DbManageField::whereRaw('db_content->"$.연락처" = "01089878824"')->get();

DbManageField::where('db_content->연락처','=','23232323232')->get();


\DB::select('select * from db_manage_fields where db_content->"$.연락처" = ?', ['01089878824']);
제가 할 수 있는 모든 방법을 시도하여 query를 해보았으나...모두 무심하게도 빈 배열만 반환하는군요...

"이름"은 잘 되는데 "연락처"는 도대체 왜 query가 안 되는 걸까요?ㅜㅜ

도와주세요...

    CommentAdd your comment...

    2 answers

    1.  
      1
      0
      -1

      저도 테스트 해봤는데 잘 되네요. 혹시나 해서 질문자님 데이터를 최대한 그대로 쓰는 상황을 만들어서 테스트 해봤습니다.

      php artisan make:migration CreateContactsTable --create contacts
      class CreateContactsTable extends Migration
      {
       public function up()
          {
              Schema::create('contacts', function (Blueprint $table) {
                  $table->increments('id');
                  $table->json('db_content');
                  $table->timestamps();
              });
          }
      }
      php artisan migrate
      php artisan make:model Contact
      class Contact extends Model
      {
          protected $fillable = ['db_content'];
      }
      php artisan tinker
      
      
      >>> App\Contact::create(['db_content' => '{"\uc774\ub984":"\uae40\ud604\uc2b9","\uc5f0\ub77d\ucc98":"01089878824","\uc0c1\ub2f4\ub0b4\uc6a9":"\ub300\ud658\ub300\ucd9c"}']);
      => App\Contact {#663
      db_content: "{"\uc774\ub984":"\uae40\ud604\uc2b9","\uc5f0\ub77d\ucc98":"01089878824","\uc0c1\ub2f4\ub0b4\uc6a9":"\ub300\ud658\ub300\ucd9c"}",
      updated_at: "2017-02-03 07:36:31",
      created_at: "2017-02-03 07:36:31",
      id: 1,
      }
      
      
      >>> App\Contact::create(['db_content' => '{"\uc774\ub984":"\ucd5c\uac11\ub9bc","\uc5f0\ub77d\ucc98":"01041719560","\uc0c1\ub2f4\ub0b4\uc6a9":"\ub300\ud658\ud558\uace0\uc2f6\uc2b5\ub2c8\ub2e4 \ub300\ucd9c\uc804\ud654\uac00\ub9ce\uc544\uc11c\ubb38\uc790\uc8fc\uc2dc\uace0 \uc804\ud654\uc8fc\uc138\uc694"}']);
      => App\Contact {#661
      db_content: "{"\uc774\ub984":"\ucd5c\uac11\ub9bc","\uc5f0\ub77d\ucc98":"01041719560","\uc0c1\ub2f4\ub0b4\uc6a9":"\ub300\ud658\ud558\uace0\uc2f6\uc2b5\ub2c8\ub2e4 \ub300\ucd9c\uc804\ud654\uac00\ub9ce\uc544\uc11c\ubb38\uc790\uc8fc\uc2dc\uace0 \uc804\ud654\uc8fc\uc138\uc694"}",
      updated_at: "2017-02-03 07:37:13",
      created_at: "2017-02-03 07:37:13",
      id: 2,
      }
      
      
      >>> App\Contact::where('db_content->연락처', '01089878824')->first();
      => App\Contact {#667
      id: 1,
      db_content: "{"이름": "김현승", "연락처": "01089878824", "상담내용": "대환대출"}",
      created_at: "2017-02-03 07:36:31",
      updated_at: "2017-02-03 07:36:31",
      }
        CommentAdd your comment...
      1.  
        1
        0
        -1

        다음과 같이 테스트 했습니다.

        $ php artisan make:model Test -m
        <?php // create_tests_table.php
        
        public function up()
        {
            Schema::create('tests', function (Blueprint $table) {
                $table->increments('id');
                $table->json('col');
                $table->timestamps();
            });
        }
        $ php artisan migrate
        $ php artisan tinker
        
        
        >>> $t = new App\Test
        => App\Test {#644}
        
        
        >>> $t->col = json_encode(['foo' => 'bar', 'baz' => 'qux'])
        => "{"foo":"bar","baz":"qux"}"
        
        
        >>> $t->save();
        => true
        
        
        >>> DB::listen(function ($q) { var_dump($q->sql); })
        => null
        
        
        >>> App\Test::where('col->foo', 'bar')->first()
        string(56) "select * from `tests` where `col`->'$."foo"' = ? limit 1"
        => App\Test {#671
             id: 1,
             col: "{"baz": "qux", "foo": "bar"}",
             created_at: "2017-02-02 14:18:48",
             updated_at: "2017-02-02 14:18:48",
           }
        
        
        >>> App\Test::where('col->baz', 'like', '%q%')->first()
        string(59) "select * from `tests` where `col`->'$."baz"' like ? limit 1"
        => App\Test {#672
             id: 1,
             col: "{"baz": "qux", "foo": "bar"}",
             created_at: "2017-02-02 14:18:48",
             updated_at: "2017-02-02 14:18:48",
           }
          CommentAdd your comment...