$builder->like()

이 메소드를 사용하면 검색에 유용한 LIKE절을 생성할 수 있습니다.

1. key/value 방법

   $builder->like('title', 2)->get()->getResult();
   
   Array
   (
       [0] => stdClass Object
           (
               [idx] => 2
               [name] => test2
               [title] => title-test2
               [email] => test2@test.com
           )
   
   )
   

   * Query : SELECT * FROM `my_table` WHERE `title` LIKE '%2%' ESCAPE '!'

   메소드 호출을 여러번 하게되면 AND와 함께 체인으로 연결됩니다.

   $builder->like('title', 2)->like('name', 2)->get()->getResult();
   
   Array
   (
       [0] => stdClass Object
           (
               [idx] => 2
               [name] => test2
               [title] => title-test2
               [email] => test2@test.com
           )
   
   )
   

   * Query : SELECT * FROM `my_table` WHERE `title` LIKE '%2%' ESCAPE '!' AND `name` LIKE '%2%' ESCAPE '!'

   와일드카드(%)의 위치를 제어하려면 옵션으로 지정된 세 번째 인수를 사용합니다. 옵션은 ‘before’, ‘after’, ‘both’(기본값)입니다.

   $builder->like('title', 2, 'before')->get()->getResult();
   
   Array
   (
       [0] => stdClass Object
           (
               [idx] => 2
               [name] => test2
               [title] => title-test2
               [email] => test2@test.com
           )
   
   )
   

   * Query : SELECT * FROM `my_table` WHERE `title` LIKE '%2' ESCAPE '!'

   $builder->like('title', 't', 'after')->get()->getResult();
   
   Array
   (
       [0] => stdClass Object
           (
               [idx] => 1
               [name] => test1
               [title] => title-test1
               [email] => test1@test.com
           )
   
       [1] => stdClass Object
           (
               [idx] => 2
               [name] => test2
               [title] => title-test2
               [email] => test2@test.com
           )
   
       [2] => stdClass Object
           (
               [idx] => 3
               [name] => test3
               [title] => title-test3
               [email] => test3@test.com
           )
   
   )
   

   * Query : SELECT * FROM `my_table` WHERE `title` LIKE 't%' ESCAPE '!'

   $builder->like('title', 2, 'both')->get()->getResult();
   
   Array
   (
       [0] => stdClass Object
           (
               [idx] => 2
               [name] => test2
               [title] => title-test2
               [email] => test2@test.com
           )
   
   )
   

   * Query : SELECT * FROM `my_table` WHERE `title` LIKE '%2%' ESCAPE '!'

2. 연관 배열 방법

   $builder->like(['title' => 2, 'name' => 2])->get()->getResult();
   
   Array
   (
       [0] => stdClass Object
           (
               [idx] => 2
               [name] => test2
               [title] => title-test2
               [email] => test2@test.com
           )
   
   )
   

   * Query : SELECT * FROM `my_table` WHERE `title` LIKE '%2%' ESCAPE '!' AND `name` LIKE '%2%' ESCAPE '!'

$builder->orLike()

이 메소드는 여러 인스턴스가 OR로 결합된다는 점을 제외하면 `like()`와 동일합니다.

$builder->like('title', 2)->orLike('name',  2)->get()->getResult();

Array
(
    [0] => stdClass Object
        (
            [idx] => 2
            [name] => test2
            [title] => title-test2
            [email] => test2@test.com
        )

    [1] => stdClass Object
        (
            [idx] => 3
            [name] => test3
            [title] => title-test3
            [email] => test3@test.com
        )

)

* Query : SELECT * FROM `my_table` WHERE `title` LIKE '%2%' ESCAPE '!' OR `name` LIKE '%3%' ESCAPE '!'

$builder->notLike()

이 메소드는 NOT LIKE문을 생성한다는 점을 제외하면 `like()`와 동일합니다.

$builder->notLike('title', 2)->get()->getResult();

Array
(
    [0] => stdClass Object
        (
            [idx] => 1
            [name] => test1
            [title] => title-test1
            [email] => test1@test.com
        )

    [1] => stdClass Object
        (
            [idx] => 3
            [name] => test3
            [title] => title-test3
            [email] => test3@test.com
        )

)

* Query : SELECT * FROM `my_table` WHERE `title` NOT LIKE '%2%' ESCAPE '!'

$builder->orNotLike()

이 메소드는 여러 인스턴스가 OR로 결합된다는 점을 제외하면 `notLike()`와 동일합니다.

$builder->like('name', 2)->orNotLike('title', 2)->get()->getResult();

Array
(
    [0] => stdClass Object
        (
            [idx] => 1
            [name] => test1
            [title] => title-test1
            [email] => test1@test.com
        )

    [1] => stdClass Object
        (
            [idx] => 3
            [name] => test3
            [title] => title-test3
            [email] => test3@test.com
        )

)

* Query : SELECT * FROM `my_table` WHERE `name` LIKE '%1%' ESCAPE '!' OR `title` NOT LIKE '%2%' ESCAPE '!'

$builder->groupBy()

검색어의 GROUP BY 부분을 작성합니다.

$builder->select('name, COUNT(*)')->groupBy('name')->get()->getResult();

Array
(
    [0] => stdClass Object
        (
            [name] => test1
            [cnt] => 1
        )

    [1] => stdClass Object
        (
            [name] => test2
            [cnt] => 1
        )

    [2] => stdClass Object
        (
            [name] => test3
            [cnt] => 1
        )

)

* Query : SELECT `name`, COUNT(*) AS cnt FROM `my_table` GROUP BY `name`

여러 값의 배열을 전달할 수도 있습니다.

$builder->select('name, title, COUNT(*) AS cnt')->groupBy(['name', 'title'])->get()->getResult();

Array
(
    [0] => stdClass Object
        (
            [name] => test1
            [title] => title-test1
            [cnt] => 1
        )

    [1] => stdClass Object
        (
            [name] => test2
            [title] => title-test2
            [cnt] => 1
        )

    [2] => stdClass Object
        (
            [name] => test3
            [title] => title-test3
            [cnt] => 1
        )

)

* Query : SELECT `name`, `title`, COUNT(*) AS cnt FROM `my_table` GROUP BY `name`, `title`

$builder->distinct()

“DISTINCT” 키워드를 쿼리에 추가합니다.

$builder->distinct()->get()->getResult();

Array
(
    [0] => stdClass Object
        (
            [idx] => 1
            [name] => test1
            [title] => title-test1
            [email] => test1@test.com
        )

    [1] => stdClass Object
        (
            [idx] => 2
            [name] => test2
            [title] => title-test2
            [email] => test2@test.com
        )

    [2] => stdClass Object
        (
            [idx] => 3
            [name] => test3
            [title] => title-test3
            [email] => test3@test.com
        )

)

* Query : SELECT DISTINCT * FROM `my_table`

$builder->having()

쿼리의 HAVING 부분을 작성합니다.

$builder->select('name, COUNT(*) AS cnt')->groupBy('name')->having('cnt',  1)->get()->getResult();

Array
(
    [0] => stdClass Object
        (
            [name] => test1
            [cnt] => 1
        )

    [1] => stdClass Object
        (
            [name] => test2
            [cnt] => 1
        )

    [2] => stdClass Object
        (
            [name] => test3
            [cnt] => 1
        )

)

* Query : SELECT `name`, COUNT(*) AS cnt FROM `my_table` GROUP BY `name` HAVING `cnt` = 1