쿼리 빌더 (Query builder)

쿼리 빌더는 정보의 추출,삽입, 업데이트를 최소한의 코드로 수행하며, 데이터베이스 어댑터에 맞게 쿼리를 자동으로 생성하여 데이터베이스에 독립된 프로그램을 만들 수 있습니다. 쿼리 생성에 사용된 값들은 자동으로 이스케이프(escape) 되므로 보안에도 유리합니다.

쿼리 빌더 실행

$this->qb->exec()

쿼리 빌더를 통하여 설정된 쿼리를 실행 합니다. 첫 번째 파리미터로 SQL 쿼리를 직접 작성하여 실행할 수도 있습니다.

$this->qb->toStr()

쿼리 빌더를 통하여 설정된 쿼리를 반환 합니다.

$sql = $this->qb
    ->from('mytable')
    ->toStr();

echo $sql;

// echo string: select * from `mytable`

데이터 조회

$this->qb->from()

FROM 부분을 사용할 수 있게 합니다

$query = $this->qb
    ->from('mytable')
    ->exec();

$this->qb->select()

쿼리에서 SELECT 부분을 직접 입력할 수 있도록 해 줍니다.

$query = $this->qb
    ->select('title, content, date')
    ->from('mytable')
    ->exec();

주석

전부(*) 를 조회할 때는 select(〈*〉) 를 사용하거나 생략 합니다. select() 함수가 생략되면 자동으로 〈SELECT *〉 를 수행합니다.

select() 함수는 옵션으로 두 번째 파라미터를 설정할 수 있습니다. 두 번째 파라미터를 FALSE 로 설정하면 쿼리 빌더는 이스케이프를 수행하지 않습니다. 복잡한 select 문을 사용할 때 유용합니다.

$query = $this->qb
    ->select("(SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id=4) AS amount_paid", FALSE)
    ->from('mytable')
    ->exec();

$this->qb->selectMax()

《SELECT MAX(field)》 부분을 사용할 수 있게 합니다. 두 번째 파라미터를 사용하면 결과값의 필드명을 다른 이름으로 바꿀 수 있습니다.

// Produces: SELECT MAX(age) as age FROM members
$query = $this->qb
    ->selectMax('age')
    ->from('members')
    ->exec();

// Produces: SELECT MAX(age) as member_age FROM members

$query = $this->qb
    ->selectMax('age', 'member_age')
    ->from('members')
    ->exec();

$this->qb->selectMin()

《SELECT MIN(field)》 부분을 사용할 수 있게 합니다. selectMax()와 같이, 두 번째 파라미터를 사용하면 결과값의 필드명을 다른 이름으로 바꿀 수 있습니다.

// Produces: SELECT MIN(age) as age FROM members
$query = $this->qb
    ->select_min('age')
    ->from('members')
    ->exec();

$this->qb->selectAvg()

《SELECT AVG(field)》 부분을 사용할 수 있게 합니다. selectMax()와 같이, 두 번째 파라미터를 사용하면 결과값의 필드명을 다른 이름으로 바꿀 수 있습니다.

// Produces: SELECT AVG(age) as age FROM members
$query = $this->qb
    ->selectAvg('age')
    ->from('members')
    ->exec();

$this->qb->selectSum()

《SELECT SUM(field)》 부분을 사용할 수 있게 합니다. selectMax()와 같이, 두 번째 파라미터를 사용하면 결과값의 필드명을 다른 이름으로 바꿀 수 있습니다.

// Produces: SELECT SUM(age) as age FROM members
$query = $this->qb
    ->selectSum('age')
    ->from('members')
    ->exec();

$this->qb->distinct()

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

// Produces: SELECT DISTINCT * FROM table
$this->qb
    ->distinct()
    ->from('table')
    ->exec();

$this->qb->join()

테이블을 JOIN 합니다.

$query = $this->qb
    ->select('*')
    ->from('blogs')
    ->join('comments', 'comments.id = blogs.id')
    ->exec();

// Produces:
// SELECT * FROM blogs JOIN comments ON comments.id = blogs.id

기본 조인(JOIN)외의 조인(left, right..)을 할 때는 세 번째 파라미터를 사용합니다. 옵션은 다음과 같습니다: left, right, outer, inner, left outer, right outer

$this->qb->join('comments', 'comments.id = blogs.id', 'left');
// Produces: LEFT JOIN comments ON comments.id = blogs.id

데이터 찾기

$this->qb->where()

이 함수는 4가지중 한 가지 방법을 사용하여 WHERE 절을 설정할 수 있게 합니다:

주석

이 함수로 전달되는 모든 변수는 자동으로 이스케이프 되어 안전한 쿼리를 생성합니다.

1. 단순 키/값 사용

   $this->qb->where('name', $name); // Produces: WHERE name = 'Joe'
   
   같다(=) 부분을 자동으로 추가해줍니다. 이 함수를 여러 번 사용하면 모두 AND 로 연결됩니다:
   

   // WHERE name = 'Joe' AND title = 'boss' AND status = 'active'
   $this->qb
       ->where('name', $name)
       ->where('title', $title)
       ->where('status', $status);
   

2. 사용자 키/값 사용

   첫 번째 파라미터에 연산기호를 삽입하여 원하는 쿼리를 만들수 있습니다

   $this->qb->where('name !=', $name);
   $this->qb->where('id <', $id); // Produces: WHERE name != 'Joe' AND id < 45
   

3. 연관배열 사용

   첫 번째 파라미터에 연관배열을 전달하여 원하는 쿼리를 만들수 있습니다.

   $array = ['name' => $name, 'title' => $title, 'status' => $status];
   $this->qb->where($array);
   // Produces: WHERE name = 'Joe' AND title = 'boss' AND status = 'active'
   

   연관배열 작성시 아래와 같이 직접 연산기호를 사용할 수도 있습니다:

   $array = ['name !=' => $name, 'id <' => $id, 'date >' => $date];
   $this->qb->where($array);
   

4. 사용자 문자열 방법

   아래와 같이 Where 절을 직접 입력할 수도 있습니다

   $where = "name='Joe' AND status='boss' OR status='active'";
   $this->qb->where($where);
   

   $this->qb->where() 에는 세 번째 파라미터가 옵션으로 제공됩니다. 이 파라미터를 FALSE로 하면, 쿼리 빌더는 필드나 데이타를 이스케이프하여 보호하지 않습니다.

   $this->qb->where('MATCH (field) AGAINST ("value")', NULL, FALSE);
   

$this->qb->orWhere()

여러 개의 조건들이 OR 로 연결되는 것을 제외하면 본 함수는 위의 함수와 동일합니다:

$this->qb->where('name !=', $name)
    ->orWhere('id >', $id);
// Produces: WHERE name != 'Joe' OR id > 50

$this->qb->whereIn()

WHERE 절의 IN (〈item〉, 〈item〉) 부분을 생성하며 복수 사용시 AND 로 연결해줍니다.

$names = ['Frank', 'Todd', 'James'];
$this->qb->whereIn('username', $names);
// Produces: WHERE username IN ('Frank', 'Todd', 'James')

$this->qb->orWhereIn()

WHERE 절의 IN (‘item’, ‘item’) 부분을 생성하며 복수 사용시 OR 로 연결해줍니다.

$names = ['Frank', 'Todd', 'James'];
$this->qb->orWhereIn('username', $names);
// Produces: OR username IN ('Frank', 'Todd', 'James')

$this->qb->whereNotIn()

WHERE 절의 NOT IN (‘item’, ‘item’) 부분을 생성하며 복수 사용시 AND 로 연결해줍니다.

$names = ['Frank', 'Todd', 'James'];
$this->qb->whereNotIn('username', $names);
// Produces: WHERE username NOT IN ('Frank', 'Todd', 'James')

$this->qb->orWhereNotIn()

WHERE 절의 NOT IN (‘item’, ‘item’) 부분을 생성하며 복수 사용시 OR 로 연결해줍니다.

$names = ['Frank', 'Todd', 'James'];
$this->qb->orWhereNotIn('username', $names);
// Produces: OR username NOT IN ('Frank', 'Todd', 'James')

유사한 데이터 검색

$this->qb->like()

이 함수는 LIKE 절을 생성해줍니다. 검색시에 유용합니다.

1. 단순 키/값 방법

   $this->qb->like('title', 'match');
   // Produces: WHERE `title` LIKE '%match%' ESCAPE '!'
   

   이 함수를 여러 번 호출하면 각각을 AND 로 연결해줍니다

   $this->qb->like('title', 'match');
   $this->qb->like('body', 'match');
   // WHERE `title` LIKE '%match%' ESCAPE '!' AND  `body` LIKE '%match% ESCAPE '!'
   

   like 절에서 와일드카드(%)를 사용해야 한다면 옵션인 세 번째 파라미터를 사용할 수 있습니다. 옵션은 다음과 같습니다: before, after, both (기본값).

   $this->qb->like('title', 'match', 'before');    // Produces: WHERE `title` LIKE '%match' ESCAPE '!'
   $this->qb->like('title', 'match', 'after');     // Produces: WHERE `title` LIKE 'match%' ESCAPE '!'
   $this->qb->like('title', 'match', 'both');      // Produces: WHERE `title` LIKE '%match%' ESCAPE '!'
   

2. 연관배열 방법

   $array = ['title' => 'match', 'page1' => 'match', 'page2' => 'match'];
   $this->qb->like($array);
   // WHERE `title` LIKE '%match%' ESCAPE '!' AND  `page1` LIKE '%match%' ESCAPE '!' AND  `page2` LIKE '%match%' ESCAPE '!'
   

$this->qb->orLike()

본 함수는 여러 개의 조건들이 OR 로 연결된 경우를 제외하고는 위 함수와 같습니다:

$this->qb
    ->like('title', 'match')
    ->orLike('body', 'match');
// WHERE `title` LIKE '%match%' ESCAPE '!' OR  `body` LIKE '%match%' ESCAPE '!'

$this->qb->notLike()

NOT LIKE 문을 생성한다는 점을 제외하면 like() 함수와 완전히 동일합니다:

$this->qb->notLike('title', 'match');  // WHERE `title` NOT LIKE '%match% ESCAPE '!'

$this->qb->orNotLike()

여러 개의 조건들이 OR 로 연결된다는점을 제외하면 notLike() 함수와 같습니다:

$this->qb->like('title', 'match');
$this->qb->orNotLike('body', 'match');
// WHERE `title` LIKE '%match% OR  `body` NOT LIKE '%match%' ESCAPE '!'

결과 그룹화 하기

$this->qb->groupBy()

GROUP BY 부분을 생성합니다

$this->qb->groupBy("title"); // Produces: GROUP BY title

여러 개의 값을 전달하기 위해서 배열도 사용할 수 있습니다:

$this->qb->groupBy(["title", "date"]);  // Produces: GROUP BY title, date

$this->qb->having()

HAVING 부분을 생성합니다. 하나의 파라미터를 사용할 수도 있고 두 개의 파라미터를 사용할 수도 있습니다:

$this->qb->having('user_id = 45');  // Produces: HAVING user_id = 45
$this->qb->having('user_id',  45);  // Produces: HAVING user_id = 45

여러 개의 값을 전달하기 위해서 배열도 사용할 수 있습니다:

$this->qb->having(['title =' => 'My Title', 'id <' => $id]);
// Produces: HAVING title = 'My Title', id < 45

쿼리 빌더는 전달되는 변수와 필드에 자동으로 이스케이프 합니다. 이 자동 이스케이프를 끄고싶다면, 세 번째 파라미터를 FALSE로 설정합니다.

$this->qb->having('user_id',  45);  // Produces: HAVING `user_id` = 45 (use MySQL DB)
$this->qb->having('user_id',  45, FALSE);  // Produces: HAVING user_id = 45

$this->qb->orHaving()

여러 조건을 《OR》 로 연결한다는 점을 제외하면 having()과 같습니다.

결과 정렬

$this->qb->orderBy()

ORDER BY 부분을 생성합니다.

첫 번째 파라미터는 정렬 기준이 되는 컬럼명입니다.

두 번째 파라미터는 정렬 방향입니다. 정렬 방향은 ASC, DESC 이 있습니다.

$this->qb->orderBy('title', 'DESC');
// Produces: ORDER BY `title` DESC

첫 번째 파라미터로 직접 문자열을 입력할 수 있습니다

$this->qb->orderBy('title DESC, name ASC');
// Produces: ORDER BY `title` DESC, `name` ASC

함수를 여러 번 호출하여 여러 필드에 대한 정렬을 수행할 수 있습니다.

$this->qb
    ->orderBy('title', 'DESC')
    ->orderBy('name', 'ASC');
// Produces: ORDER BY `title` DESC, `name` ASC

Limiting or Counting Results

$this->qb->limit()

쿼리 결과로 리턴받을 열의 개수를 설정할 때 사용합니다:

$this->qb->limit(10);  // Produces: LIMIT 10

두 번째 파라미터는 오프셋을 설정할 때 사용합니다.

$this->qb->limit(10, 20);  // Produces: LIMIT 20, 10 (use MySQL)

$this->qb->getCount()

레코드의 수를 반환합니다.

주석

exec()를 함께 사용하지 않습니다.

// Produces an integer, like 25
echo $this->qb
    ->from('my_table')
    ->getCount();

// Produces an integer, like 17
$this->qb
    ->like('title', 'match')
    ->from('my_table')
    ->getCount();

파라미터로 컬럼명을 전달할 수 있습니다.

// Produces an integer, like 25
echo $this->qb
    ->from('my_table')
    ->getCount('id');

쿼리 그룹화

쿼리 그루핑은 WHERE 절에 괄호를 추가하여 그룹을 생성할 수 있게 합니다. WHERE 절에 복잡한 쿼리를 생성 가능하게 합니다. 중첩된 그룹도 지원됩니다.

$this->qb
    ->select('*')
    ->from('my_table')
    ->groupStart()
        ->where('a', 'a')
        ->orGroupStart()
            ->where('b', 'b')
            ->where('c', 'c')
        ->groupEnd()
    ->groupEnd()
    ->where('d', 'd')
->exec();

// Generates:
// SELECT * FROM (`my_table`) WHERE ( `a` = 'a' OR ( `b` = 'b' AND `c` = 'c' ) ) AND `d` = 'd'

주석

그룹은 균형을 이루어야 합니다. 모든 groupStart() 는 groupEnd() 와 함께 쓰여져야 합니다.

$this->qb->groupStart()

쿼리의 WHERE 절에 여는 괄호를 더하여 현재 그룹을 시작합니다.

$this->qb->orGroupStart()

쿼리의 WHERE 절에 여는 괄호를 더하여 현재 그룹을 시작합니다. 〈OR〉 으로 중간을 연결합니다.

$this->qb->notGroupStart()

쿼리의 WHERE 절에 여는 괄호를 더하여 현재 그룹을 시작합니다. 〈NOT〉 으로 중간을 연결합니다.

$this->qb->orNotGroupStart()

쿼리의 WHERE 절에 여는 괄호를 더하여 현재 그룹을 시작합니다. 〈OR NOT〉 으로 중간을 연결합니다.

$this->qb->groupEnd()

쿼리의 WHERE 절에 닫는 괄호를 더하여 현재 그룹을 닫습니다.

Inserting Data

$this->qb->insert()

데이터를 이용하여 Insert 쿼리를 생성한 후 실행합니다. 데이터는 배열 혹은 객체를 사용하여 제공합니다. 다음은 배열을 사용한 예입니다.

$data = [
    'title' => 'My title',
    'name' => 'My Name',
    'date' => 'My date'
];

$this->qb
    ->insert('mytable', $data)
    ->exec();
// Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date')

첫 번째 파라미터는 테이블명, 두 번째 파라미터는 필드명과 값을 포함한 연관배열 입니다.

다음은 객체를 사용한 예제입니다.

/*
class Myclass {
    public $title = 'My Title';
    public $content = 'My Content';
    public $date = 'My Date';
}
*/

$object = new Myclass;
$this->qb
    ->insert('mytable', $object)
    ->exec();
// Produces: INSERT INTO mytable (title, content, date) VALUES ('My Title', 'My Content', 'My Date')

첫 번째 파라미터는 테이블 명이며, 두 번째 파라미터는 값에 대한 객체입니다.

주석

전달된 모든 값은 자동으로 이스케이프되어 보다 안전한 쿼리로 생성됩니다.

Updating Data

$this->qb->update()

제공된 데이터를 가지고 업데이트 쿼리를 생성하고 실행합니다. 당신은 배열 혹은 객체를 사용하여 데이터를 전달할 수 있습니다. 다음은 배열을 사용한 예입니다:

$data = [
    'title' => $title,
    'name' => $name,
    'date' => $date
];

$this->qb
    ->where('id', $id)
    ->update('mytable', $data);
// Produces:
//
//      UPDATE mytable
//      SET title = '{$title}', name = '{$name}', date = '{$date}'
//      WHERE id = $id

주석

update() 는 반드시 where() 함수와 함께 사용하여야 합니다. 사용하지 않은 경우 에러가 발생합니다.

아래는 객체를 사용한 예입니다:

/*
class Myclass {
    public $title = 'My Title';
    public $content = 'My Content';
    public $date = 'My Date';
}
*/

$object = new Myclass;
$this->qb
    ->where('id', $id)
    ->update('mytable', $object);
// Produces:
//
//      UPDATE mytable
//      SET title = '{$title}', name = '{$name}', date = '{$date}'
//      WHERE id = $id

주석

전달된 모든 값은 자동으로 이스케이프 됩니다.

데이터 설정

$this->qb->set()

이 함수는 입력(insert) 혹은 업데이트(update)시 값을 설정할 수 있도록 합니다.

이 함수는 입력,업데이트시 데이터 배열을 사용하는 방법 대신 사용할 수 있습니다.

$this->qb
    ->set('name', $name)
    ->update('mytable')
    ->where('id', 1)
    ->exec();
// Produces:
//
//      UPDATE mytable
//      SET name = '{$name}'
//      WHERE id = 1

함수를 여러 번 호출했을 때는 입력인지 업데이트인지에 따라 적절하게 자동으로 연결해줍니다.

$this->qb
    ->set('name', $name)
    ->set('title', $title)
    ->set('status', $status)
    ->insert('mytable')
    ->exec();
// Produces:
//
//      INSERT INTO mytable (title, content, date) VALUES ('{$name}', '{$title}', '{$status}')

set() 함수는 옵션으로 세 번째 파라미터(escape)를 받아들입니다. 이 파라미터를 FALSE로 하면 쿼리를 자동으로 이스케이프 하지않습니다. 아래예제는 set() 함수를 호출할 때 세 번째 파라미터를 사용한 것과 안한 것을 비교하여 보여줍니다.

$this->qb
    ->set('field', 'field+1', FALSE)
    ->insert('mytable')
    ->exec();
// INSERT INTO mytable (field) VALUES (field+1)

$this->qb
    ->set('field', 'field+1')
    ->insert('mytable')
    ->exec();
// INSERT INTO mytable (field) VALUES ('field+1')

이 함수를 사용할 때 연관배열을 사용할 수도 있습니다:

$array = [
    'name' => $name,
    'title' => $title,
    'status' => $status
];

$this->qb
    ->set($array)
    ->insert('mytable')
    ->exec();

객체도 가능합니다

/*
class Myclass {
    public $title = 'My Title';
    public $content = 'My Content';
    public $date = 'My Date';
}
*/

$object = new Myclass;
$this->qb
    ->set($object)
    ->insert('mytable')
    ->exec();

Deleting Data

$this->qb->delete()

삭제 쿼리를 생성하고 실행합니다.

$this->qb
    ->where('id', 1)
    ->delete('mytable')
    ->exec();
// Produces:
//
//      DELETE FROM mytable
//      WHERE id = 1

주석

delete() 는 반드시 where() 함수와 함께 사용하여야 합니다. 사용하지 않은 경우 에러가 발생합니다.

파라미터는 테이블 이름입니다.

쿼리 빌더 캐싱

쿼리 빌더는 호출(exec)이 완료되면 쿼리를 만들기 위해 저장된 데이터를 다음 호출을 위해서 리셋합니다. 캐싱을 사용하면 이러한 리셋을 막아 다음 쿼리를 만들때 재사용할 수 있습니다.

캐시는 누적됩니다. 만약 2개의 캐시된 select() 호출과 2개의 캐시되지않은 select() 호출이 있다면, 4개의 select() 를 호출한 결과가 됩니다. 캐시를 위한 함수는 3개가 있습니다.

$this->qb->startCache()

이 함수는 캐싱을 시작하기 위하여 반드시 호출되어야 합니다. 캐싱이 지원되는 모든 쿼리는 나중을 위해서 저장됩니다.

$this->qb->stopCache()

이 함수는 캐싱을 정지하기 위하여 호출합니다.

$this->qb->flushCache()

이 함수는 쿼리 빌더 캐시로 저장된 모든 아이템을 제거합니다.

사용 예

$this->qb
    ->startCache()
    ->select('field1')
    ->stop_cache()
    ->from('tablename')
    ->exec();
//Generates: SELECT `field1` FROM (`tablename`)

$this->qb
    ->select('field2')
    ->from('tablename')
    ->exec();
//Generates:  SELECT `field1`, `field2` FROM (`tablename`)

$this->qb
    ->flushCache()
    ->select('field2')
    ->from('tablename')
    ->exec();
//Generates:  SELECT `field2` FROM (`tablename`)

주석

다음의 쿼리문은 캐시될 수 있습니다: select, from, join, where, like, group_by, having, order_by