CI4

[코드이그나이터] 쿼리 빌더

으누아빠 2020. 9. 7. 18:37
반응형

쿼리 빌더

출처:
http://ci4doc.cikorea.net/database/query_builder.html

쿼리 빌더 로드

데이터베이스 연결의 table() 메소드를 통해 로드

$db      = \Config\Database::connect();
$builder = $db->table('users');

$builder->get()

SQL SELECT 문을 작성

$builder = $db->table('mytable');
$query   = $builder->get();  // Produces: SELECT * FROM mytable

첫 번째와 두 번째 매개 변수를 사용하여 limit과 offset을 설정

$query = $builder->get(10, 20);

// Executes: SELECT * FROM mytable LIMIT 20, 10
// (in MySQL. Other databases have slightly different syntax)

$query = $builder->get();

foreach ($query->getResult() as $row)
{
        echo $row->title;
}

$builder->getWhere()

db->where() 함수를 사용하는 대신 첫 번째 매개 변수에 "where"절을 추가

$query = $builder->getWhere(['id' => $id], $limit, $offset);

$builder->select()

쿼리의 SELECT 부분

$builder->select('title, content, date');
$query = $builder->get();

// Executes: SELECT title, content, date FROM mytable

// 두번째 파라미터를 FALSE 로 설정하면 필드 또는 테이블 이름을 보호하지 않음
$builder->select('(SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id=4) AS amount_paid', FALSE);
$query = $builder->get();

$builder->selectMax()

쿼리의 SELECT MAX(field) 부분을 작성

두번째 파리미터를 설정할경우 결과 필드명을 수정할 수 있음

$builder->selectMax('age');
$query = $builder->get();  // Produces: SELECT MAX(age) as age FROM mytable

$builder->selectMax('age', 'member_age');
$query = $builder->get(); // Produces: SELECT MAX(age) as member_age FROM mytable

$builder->selectMin()

쿼리의 SELECT SELECT MIN(field) 부분을 작성

두번째 파리미터를 설정할경우 결과 필드명을 수정할 수 있음

$builder->selectMin('age');
$query = $builder->get(); // Produces: SELECT MIN(age) as age FROM mytable

$builder->selectAvg()

쿼리의 SELECT AVG(field) 부분을 작성

두번째 파리미터를 설정할경우 결과 필드명을 수정할 수 있음

$builder->selectAvg('age');
$query = $builder->get(); // Produces: SELECT AVG(age) as age FROM mytable

$builder->selectSum()

쿼리의 SELECT SUM(field) 부분을 작성

두번째 파리미터를 설정할경우 결과 필드명을 수정할 수 있음

$builder->selectSum('age');
$query = $builder->get(); // Produces: SELECT SUM(age) as age FROM mytable

$builder->selectCount()

쿼리의 SELECT COUNT(field) 부분을 작성

두번째 파리미터를 설정할경우 결과 필드명을 수정할 수 있음

groupBy() 와 함께 사용할때 유용한 메서드

$builder->selectCount('age');
$query = $builder->get(); // Produces: SELECT COUNT(age) as age FROM mytable

$builder->from()

쿼리의 FROM 부분을 작성

$builder->select('title, content, date');
$builder->from('mytable');
$query = $builder->get();  // Produces: SELECT title, content, date FROM mytable

$builder->join()

세 번째 파라미터를 통해 JOIN의 유형을 결정할 수 있음 제공 옵션 : left, right, outer, inner, left outer, right outer.

$builder->db->table('blog');
$builder->select('*');
$builder->join('comments', 'comments.id = blogs.id');
$query = $builder->get();

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

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

$builder->where()

$builder->where('name', $name); // Produces: WHERE name = 'Joe'
$builder->where('name', $name);
$builder->where('title', $title);
$builder->where('status', $status);
// WHERE name = 'Joe' AND title = 'boss' AND status = 'active'
$builder->where('name !=', $name);
$builder->where('id <', $id); // Produces: WHERE name != 'Joe' AND id < 45
$array = ['name' => $name, 'title' => $title, 'status' => $status];
$builder->where($array);
// Produces: WHERE name = 'Joe' AND title = 'boss' AND status = 'active'

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

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

세번째 파라미터를 사용하여 FALSE를 설정하면 CodeIgniter는 필드 또는 테이블 이름을 보호하지 않음

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

서브쿼리

$builder->where('advance_amount <', function(BaseBuilder $builder) {
        return $builder->select('MAX(advance_amount)', false)->from('orders')->where('id >', 2);
});
// Produces: WHERE "advance_amount" < (SELECT MAX(advance_amount) FROM "orders" WHERE "id" > 2)

$builder->orWhere()

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

$builder->whereIn()

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

//값 배열 대신 서브 쿼리를 사용할 수 있음
$builder->whereIn('id', function(BaseBuilder $builder) {
        return $builder->select('job_id')->from('users_jobs')->where('user_id', 3);
});
// Produces: WHERE "id" IN (SELECT "job_id" FROM "users_jobs" WHERE "user_id" = 3)

$builder->orWhereIn()

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

$builder->orWhereIn('id', function(BaseBuilder $builder) {
        return $builder->select('job_id')->from('users_jobs')->where('user_id', 3);
});

// Produces: OR "id" IN (SELECT "job_id" FROM "users_jobs" WHERE "user_id" = 3)

$builder->whereNotIn()

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

$builder->whereNotIn('id', function(BaseBuilder $builder) {
        return $builder->select('job_id')->from('users_jobs')->where('user_id', 3);
});

// Produces: WHERE "id" NOT IN (SELECT "job_id" FROM "users_jobs" WHERE "user_id" = 3)

$builder->orWhereNotIn()

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

$builder->orWhereNotIn('id', function(BaseBuilder $builder) {
        return $builder->select('job_id')->from('users_jobs')->where('user_id', 3);
});

// Produces: OR "id" NOT IN (SELECT "job_id" FROM "users_jobs" WHERE "user_id" = 3)

$builder->like()

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

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

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

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

$builder->orLike()

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

$builder->notLike()

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

$builder->orNotLike()

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

$builder->groupBy()

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

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

$builder->distinct()

$builder->distinct();
$builder->get(); // Produces: SELECT DISTINCT * FROM mytable

$builder->having()

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

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

//CodeIgniter는 기본적으로 쿼리를 escape하여  데이터베이스에 전송
//이스케이프되는 것을 방지하고 싶다면 옵션으로 지정된 세 번째 인수를 FALSE로 설정

$builder->having('user_id',  45);  // Produces: HAVING `user_id` = 45 in some databases such as MySQL
$builder->having('user_id',  45, FALSE);  // Produces: HAVING user_id = 45

$builder->orHaving()

having()와 동일하며 여러 절을 “OR”로 구분

$builder->havingIn()

$groups = [1, 2, 3];
$builder->havingIn('group_id', $groups);
// Produces: HAVING group_id IN (1, 2, 3)

$builder->havingIn('id', function(BaseBuilder $builder) {
        return $builder->select('user_id')->from('users_jobs')->where('group_id', 3);
});
// Produces: HAVING "id" IN (SELECT "user_id" FROM "users_jobs" WHERE "group_id" = 3)

$builder->orHavingIn()

$groups = [1, 2, 3];
$builder->orHavingIn('group_id', $groups);
// Produces: OR group_id IN (1, 2, 3)

$builder->orHavingIn('id', function(BaseBuilder $builder) {
        return $builder->select('user_id')->from('users_jobs')->where('group_id', 3);
});

// Produces: OR "id" IN (SELECT "user_id" FROM "users_jobs" WHERE "group_id" = 3)

$builder->havingNotIn()

$groups = [1, 2, 3];
$builder->havingNotIn('group_id', $groups);
// Produces: HAVING group_id NOT IN (1, 2, 3)

$builder->havingNotIn('id', function(BaseBuilder $builder) {
        return $builder->select('user_id')->from('users_jobs')->where('group_id', 3);
});

// Produces: HAVING "id" NOT IN (SELECT "user_id" FROM "users_jobs" WHERE "group_id" = 3)

$builder->orHavingNotIn()

$groups = [1, 2, 3];
$builder->havingNotIn('group_id', $groups);
// Produces: OR group_id NOT IN (1, 2, 3)

$builder->orHavingNotIn('id', function(BaseBuilder $builder) {
        return $builder->select('user_id')->from('users_jobs')->where('group_id', 3);
});

// Produces: OR "id" NOT IN (SELECT "user_id" FROM "users_jobs" WHERE "group_id" = 3)

$builder->havingLike()

$builder->havingLike('title', 'match');
// Produces: HAVING `title` LIKE '%match%' ESCAPE '!'

$builder->havingLike('title', 'match');
$builder->havingLike('body', 'match');
// HAVING `title` LIKE '%match%' ESCAPE '!' AND  `body` LIKE '%match% ESCAPE '!'

$builder->havingLike('title', 'match', 'before');       // Produces: HAVING `title` LIKE '%match' ESCAPE '!'
$builder->havingLike('title', 'match', 'after');        // Produces: HAVING `title` LIKE 'match%' ESCAPE '!'
$builder->havingLike('title', 'match', 'both'); // Produces: HAVING `title` LIKE '%match%' ESCAPE '!'

$array = ['title' => $match, 'page1' => $match, 'page2' => $match];
$builder->havingLike($array);
// HAVING `title` LIKE '%match%' ESCAPE '!' AND  `page1` LIKE '%match%' ESCAPE '!' AND  `page2` LIKE '%match%' ESCAPE '!'

$builder->orHavingLike()

$builder->havingLike('title', 'match'); $builder->orHavingLike('body', $match);
// HAVING `title` LIKE '%match%' ESCAPE '!' OR  `body` LIKE '%match%' ESCAPE '!'

$builder->notHavingLike()

$builder->notHavingLike('title', 'match');
// HAVING `title` NOT LIKE '%match% ESCAPE '!'

$builder->orNotHavingLike()

$builder->havingLike('title', 'match');
$builder->orNotHavingLike('body', 'match');
// HAVING `title` LIKE '%match% OR  `body` NOT LIKE '%match%' ESCAPE '!'

$builder->orderBy()

두 번째 파라미터를 사용하면 정렬 방향을 설정할 수 있음. 값은 ASC, DESC, RANDOM.


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

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

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

$builder->orderBy('title', 'RANDOM');
// Produces: ORDER BY RAND()

$builder->orderBy(42, 'RANDOM');
// Produces: ORDER BY RAND(42)

$builder->limit()

쿼리에서 반환하려는 행 수를 제한


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

$builder->limit(10, 20);  // Produces: LIMIT 20, 10 (in MySQL. Other databases have slightly different syntax)

$builder->countAllResults()

쿼리 빌더를 통해 조건에 맞는 행의 갯수를 반환


echo $builder->countAllResults();  // Produces an integer, like 25
$builder->like('title', 'match');
$builder->from('my_table');
echo $builder->countAllResults(); // Produces an integer, like 17

$builder->countAll()

특정 테이블의 모든 행의 갯수를 반환

echo $builder->countAll();  // Produces an integer, like 25

쿼리 그룹화

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

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

$builder->insert()

제공한 데이터를 기반으로 Insert 문자열을 생성하고 쿼리를 실행

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

$builder->insert($data);
// 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;
$builder->insert($object);
// Produces: INSERT INTO mytable (title, content, date) VALUES ('My Title', 'My Content', 'My Date')

$builder->ignore()

동일한 기본 키를 가진 항목이 이미 있으면 쿼리가 인서트(insert)되지 않음

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

$builder->ignore(true)->insert($data);
// Produces: INSERT OR IGNORE INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date')

$builder->replace()

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

$builder->replace($data);

// Executes: REPLACE INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date')

$builder->set()

$builder->set('name', $name);
$builder->insert();  // Produces: INSERT INTO mytable (`name`) VALUES ('{$name}')

$builder->set('name', $name);
$builder->set('title', $title);
$builder->set('status', $status);
$builder->insert();

$builder->set('field', 'field+1', FALSE);
$builder->where('id', 2);
$builder->update(); // gives UPDATE mytable SET field = field+1 WHERE `id` = 2

$builder->set('field', 'field+1');
$builder->where('id', 2);
$builder->update(); // gives UPDATE `mytable` SET `field` = 'field+1' WHERE `id` = 2

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

$builder->set($array);
$builder->insert();

=================================================

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

$object = new Myclass;
$builder->set($object);
$builder->insert();

$builder->update()

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

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

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

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

$builder->delete()

$builder->delete(['id' => $id]);  // Produces: // DELETE FROM mytable  // WHERE id = $id

$builder->where('id', $id);
$builder->delete();

// Produces:
// DELETE FROM mytable
// WHERE id = $id

$builder->emptyTable()

$builder->emptyTable('mytable'); // Produces: DELETE FROM mytable

$builder->truncate()

$builder->truncate();

// Produce:
// TRUNCATE mytable

메소드 체이닝(Chaining)

여러 함수를 연결하여 구문을 단순화

$query = $builder->select('title')
                 ->where('id', $id)
                 ->limit(10, 20)
                 ->get();

'CI4' 카테고리의 다른 글

[코드이그나이터] 데이터베이스 메타데이터  (0) 2020.09.07
[코드이그나이터] 트랜잭션(Transactiion)  (0) 2020.09.07
[코드이그나이터] 쿼리 헬퍼 메소드  (0) 2020.09.07
[코드이그나이터] 쿼리 결과 생성  (0) 2020.09.07
[코드이그나이터] 쿼리실행  (0) 2020.09.07

'CI4'의 다른글

  • 현재글[코드이그나이터] 쿼리 빌더

관련글

댓글

티스토리툴바