laravel에서 json column을 where로 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를 해보았으나...모두 무심하게도 빈 배열만 반환하는군요...

2 answers

1.  

   1

   0

   -1

   • 이현석
   • Feb 03, 2017

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

   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",
   }

   •  Comment

   CommentAdd your comment...
2.  

   1

   0

   -1

   • 김주원
   • Feb 02, 2017

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

   $ 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",
      }

   •  Comment

   CommentAdd your comment...