Skip to content

01.QueryBuilder

Jump to bottom
Muhammet SAFAK edited this page Jun 29, 2022 · 4 revisions

Query Builder Yöntemleri ve Kullanımı

InitPHP Database kütüphanesinin detaylı kullanımına geçmeden önce mutlaka bilmeniz gereken konuların başında Query Builder'ın kullanımı geliyor.

Bu dökümantasyon InitPHP Database kütüphanesinin Query Builder yöntemlerini açıklamaktadır.

Query Builder nedir?

Query Builder, kullanılan SQL sürücüsünden bağımsız olarak PHP söz dizimi kullanılarak SQL cümleleri kurmanızı sağlayan sınıf/kitaplığa verilen isimdir.

Query Builder Yöntemleri

select()

public function select(string ...$columns): self;

Seçilecek sütun isimlerini bildirir. Detaylı kullanım örnekleri ve karşılıklarına aşağıdaki örnekte bulabilirsiniz.

/** @var \InitPHP\Database\DB $db */

// SELECT `name`
$db->select('name');

// SELECT `name`, `mail`
$db->select('name, mail');

// SELECT `name`, `mail`
$db->select('name', 'mail');

// SELECT `username` AS `name`
$db->select('username as name'); 

// SELECT `user`.`name` AS `u_name`, `post`.`id` AS `post_id`
$db->select('user.name as u_name', 'post.id as post_id');

// SELECT COUNT(`name`)
$db->select('count(name)');

selectCount()

public function selectCount(string $column): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT COUNT(`name`)
$db->selectCount('name');

selectMax()

public function selectMax(string $column): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT MAX(`name`)
$db->selectMax('name');

selectMin()

public function selectMin(string $column): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT MIN(`name`)
$db->selectMin('name');

selectAvg()

public function selectAvg(string $column): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT AVG(`name`)
$db->selectAvg('name');

selectAs()

public function selectAs(string $column, string $alias): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT `name` AS `n`
$db->selectAs('name', 'n');

selectUpper()

public function selectUpper(string $column): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT UPPER(`name`)
$db->selectUpper('name');

selectLower()

public function selectLower(string $column): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT LOWER(`name`)
$db->selectLower('name');

selectLength()

public function selectLength(string $column): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT LENGTH(`name`)
$db->selectLength('name');

selectMid()

public function selectMid(string $column, int $offset, int $length): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT MID(`username`, 2, 4)
$db->selectMid('username', 2, 4);

selectLeft()

public function selectMid(string $column, int $length): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT LEFT(`username`, 2)
$db->selectLeft('username', 2);

selectRight()

public function selectRight(string $column, int $length): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT RIGHT(`username`, 2)
$db->selectRight('username', 2);

selectDistinct()

public function selectDistinct(string $column): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT DISTINCT(`username`)
$db->selectDistinct('username');

selectCoalesce()

public function selectCoalesce(string $column, string|int $default = '0'): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT COALESCE(`username`, 0)
$db->selectCoalesce('username', 0);

selectSum()

public function selectSum(string $column): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT SUM(`userId`)
$db->selectSum('userId');

from()

public function from(string $table): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// FROM `user`
$db->from('user');

// FROM `user`, `post`
$db->from('user, post');

// FROM `user` AS `u`, `post` AS `p`
$db->from('user as u, post as p');

// FROM `user` AS `u`, `post` AS `p`
$db->from('user u, post p');

join()

public function join(string $table, string $onStmt, string $type = 'INNER'): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// FROM `user` INNER JOIN `post` ON `post`.`author`=`user`.`id`
$db->from('user')
    ->join('post', 'post.author=user.id');

// FROM `user` AS `u` INNER JOIN `post` AS `p` ON `p`.`author`=`u`.`id`
$db->from('user u')
    ->join('post p', 'post.author=user.id');

selfJoin()

public function selfJoin(string $table, string $onStmt): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// FROM `user`, `post` WHERE `post`.`author`=`user`.`id`
$db->from('user')
    ->selfJoin('post', 'post.author=user.id');

// FROM `user` AS `u`, `post` AS `p` WHERE `p`.`author`=`u`.`id`
$db->from('user u')
    ->selfJoin('post p', 'post.author=user.id');

innerJoin()

public function innerJoin(string $table, string $onStmt): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// FROM `user` INNER JOIN `post` ON `post`.`author`=`user`.`id`
$db->from('user')
    ->innerJoin('post', 'post.author=user.id');

// FROM `user` AS `u` INNER JOIN `post` AS `p` ON `p`.`author`=`u`.`id`
$db->from('user u')
    ->innerJoin('post p', 'post.author=user.id');

leftJoin()

public function leftJoin(string $table, string $onStmt): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// FROM `user` LEFT JOIN `post` ON `post`.`author`=`user`.`id`
$db->from('user')
    ->leftJoin('post', 'post.author=user.id');

// FROM `user` AS `u` LEFT JOIN `post` AS `p` ON `p`.`author`=`u`.`id`
$db->from('user u')
    ->leftJoin('post p', 'post.author=user.id');

rightJoin()

public function rightJoin(string $table, string $onStmt): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// FROM `user` RIGHT JOIN `post` ON `post`.`author`=`user`.`id`
$db->from('user')
    ->rightJoin('post', 'post.author=user.id');

// FROM `user` AS `u` RIGHT JOIN `post` AS `p` ON `p`.`author`=`u`.`id`
$db->from('user u')
    ->rightJoin('post p', 'post.author=user.id');

rightOuterJoin()

public function rightOuterJoin(string $table, string $onStmt): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// FROM `user` RIGHT OUTER JOIN `post` ON `post`.`author`=`user`.`id`
$db->from('user')
    ->rightOuterJoin('post', 'post.author=user.id');

// FROM `user` AS `u` RIGHT OUTER JOIN `post` AS `p` ON `p`.`author`=`u`.`id`
$db->from('user u')
    ->rightOuterJoin('post p', 'post.author=user.id');

leftOuterJoin()

public function leftOuterJoin(string $table, string $onStmt): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// FROM `user` LEFT OUTER JOIN `post` ON `post`.`author`=`user`.`id`
$db->from('user')
    ->leftOuterJoin('post', 'post.author=user.id');

// FROM `user` AS `u` LEFT OUTER JOIN `post` AS `p` ON `p`.`author`=`u`.`id`
$db->from('user u')
    ->leftOuterJoin('post p', 'post.author=user.id');

where()

public function where(string $column, mixed $value, string $mark = '=', string $logical = 'AND'): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// FROM `user` WHERE `id` = 15
$db->from('user')
    ->where('id', 15);

// FROM `user` WHERE `name` = "admin"
$db->from('user')
    ->where('name', 'admin');

// FROM `user` WHERE `id` = :id
$db->from('user')
    ->where('id', ':id');
$db->setParameter(':id', 15);

andWhere()

public function andWhere(string $column, mixed $value, string $mark = '='): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// FROM `user` WHERE `id` = 15
$db->from('user')
    ->andWhere('id', 15);

// FROM `user` WHERE `name` = "admin"
$db->from('user')
    ->andWhere('name', 'admin');

// FROM `user` WHERE `id` = :id
$db->from('user')
    ->andWhere('id', ':id');
$db->setParameter(':id', 15);

orWhere()

public function orWhere(string $column, mixed $value, string $mark = '='): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */
// FROM `user` WHERE `name` = "admin" OR `id` = 15
$db->from('user')
    ->orWhere('name', 'admin')
    ->orWhere('id', 15);

// FROM `user` WHERE `id` = :id OR `name` = :username
$db->from('user')
    ->orWhere('id', ':id')
    ->orWhere('name', ':username');
$db->setParameter(':id', 15);
$db->setParameter(':username', 'admin');

having()

public function having(string $column, mixed $value, string $mark = '=', string $logical = 'AND'): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT `publisher`, COUNT(`book_name`) FROM `books` GROUP BY `publisher` HAVING SUM(`book_size`) > 5
$db->from('books')
    ->select('publisher')
    ->selectCount('book_name as book_size')
    ->groupBy('publisher')
    ->having('SUM(book_size)', 5, '>');

groupBy()

public function groupBy(string $column): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT `publisher`, COUNT(`book_name`) FROM `books` GROUP BY `publisher` HAVING SUM(`book_size`) > 5
$db->from('books')
    ->select('publisher')
    ->selectCount('book_name as book_size')
    ->groupBy('publisher')
    ->having('SUM(book_size)', 5, '>');

orderBy()

public function orderBy(string $column, string $soft = 'ASC'): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT `post_id` FROM `post` WHERE `status` = 1 ORDER BY `post_id` DESC, `post_date` ASC
$db->select('post_id')
    ->from('post')
    ->where('status', 1)
    ->orderBy('post_id', 'DESC')
    ->orderBy('post_date', 'ASC');

offset()

public function offset(int $offset = 0): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT `post_id` FROM `post` WHERE `status` = 1 ORDER BY `post_id` DESC, `post_date` ASC LIMIT 10, 20
$db->select('post_id')
    ->from('post')
    ->where('status', 1)
    ->orderBy('post_id', 'DESC')
    ->orderBy('post_date', 'ASC')
    ->offset(10)
    ->limit(20);

limit()

public function limit(int $limit = 0): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT `post_id` FROM `post` WHERE `status` = 1 ORDER BY `post_id` DESC, `post_date` ASC LIMIT 10, 20
$db->select('post_id')
    ->from('post')
    ->where('status', 1)
    ->orderBy('post_id', 'DESC')
    ->orderBy('post_date', 'ASC')
    ->offset(10)
    ->limit(20);

beetween()

public function between(string $column, array $values, string $logical = 'AND'): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT `post_id` FROM `post` WHERE `status` = 1 AND `post_id` BETWEEN 15 AND 20
$db->select('post_id')
    ->from('post')
    ->where('status', 1)
    ->between('post_id', [15, 20]);

notBetween()

public function notBetween(string $column, array $values, string $logical = 'AND'): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT `post_id` FROM `post` WHERE `status` = 1 AND `post_id` NOT BETWEEN 15 AND 20
$db->select('post_id')
    ->from('post')
    ->where('status', 1)
    ->notBetween('post_id', [15, 20]);

findInSet()

public function findInSet(string $column, null|string|string[]|int[] $value, string $logical = 'AND'): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT `post_id` FROM `post` WHERE `status` = 1 AND FIND_IN_SET("15,20", group)
$db->select('post_id')
    ->from('post')
    ->where('status', 1)
    ->findInSet('group', ['A', 'B']);

// // SELECT `post_id` FROM `post` WHERE `status` = 1 AND FIND_IN_SET(NULL, group)
$db->select('post_id')
    ->from('post')
    ->where('status', 1)
    ->findInSet('group', null);

in()

public function in(string $column, string[]|int[] $value, string $logical = 'AND'): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT `post_id` FROM `post` WHERE `status` = 1 AND `post_id` IN (3, 5, 13)
$db->select('post_id')
    ->from('post')
    ->where('status', 1)
    ->in('post_id', [3, 5, 13]);

notIn()

public function notIn(string $column, string[]|int[] $value, string $logical = 'AND'): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT `post_id` FROM `post` WHERE `status` = 1 AND `post_id` NOT IN (3, 5, 13)
$db->select('post_id')
    ->from('post')
    ->where('status', 1)
    ->notIn('post_id', [3, 5, 13]);

regexp()

public function regexp(string $column, string $value, string $logical = 'AND'): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT `post_id` FROM `post` WHERE `status` = 1 AND `post_title` REGEXP "^(php|python)"
$db->select('post_id')
    ->from('post')
    ->where('status', 1)
    ->regexp('post_title', '^(php|python)');

like()

public function like(string $column, string $value, string $logical = 'AND'): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT `post_id` FROM `post` WHERE `status` = 1 AND `post_title` LIKE "%php%"
$db->select('post_id')
    ->from('post')
    ->where('status', 1)
    ->like('post_title', 'php');

startLike()

public function startLike(string $column, string $value, string $logical = 'AND'): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT `post_id` FROM `post` WHERE `status` = 1 AND `post_title` LIKE "%php"
$db->select('post_id')
    ->from('post')
    ->where('status', 1)
    ->startLike('post_title', 'php');

endLike()

public function endLike(string $column, string $value, string $logical = 'AND'): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT `post_id` FROM `post` WHERE `status` = 1 AND `post_title` LIKE "php%"
$db->select('post_id')
    ->from('post')
    ->where('status', 1)
    ->endLike('post_title', 'php');

notLike()

public function notLike(string $column, string $value, string $logical = 'AND'): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT `post_id` FROM `post` WHERE `status` = 1 AND `post_title` NOT LIKE "%php%"
$db->select('post_id')
    ->from('post')
    ->where('status', 1)
    ->notLike('post_title', 'php');

startNotLike()

public function startNotLike(string $column, string $value, string $logical = 'AND'): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT `post_id` FROM `post` WHERE `status` = 1 AND `post_title` NOT LIKE "%php"
$db->select('post_id')
    ->from('post')
    ->where('status', 1)
    ->startNotLike('post_title', 'php');

endNotLike()

public function endNotLike(string $column, string $value, string $logical = 'AND'): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT `post_id` FROM `post` WHERE `status` = 1 AND `post_title` NOT LIKE "php%"
$db->select('post_id')
    ->from('post')
    ->where('status', 1)
    ->endNotLike('post_title', 'php');

soundex()

public function soundex(string $column, string $value, string $logical = 'AND'): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT `post_id` FROM `post` WHERE `status` = 1 AND SOUNDEX(post_title) LIKE CONCAT('%', TRIM(TRAILING '0' FROM SOUNDEX("php")), '%')
$db->select('post_id')
    ->from('post')
    ->where('status', 1)
    ->soundex('post_title', 'php');

is()

public function is(string $column, string|null $value, string $logical = 'AND'): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT `post_id` FROM `post` WHERE `status` = 1 AND `post_title` IS "php"
$db->select('post_id')
    ->from('post')
    ->where('status', 1)
    ->is('post_title', 'php');

// SELECT `post_id` FROM `post` WHERE `status` = 1 AND `post_title` IS NULL
$db->select('post_id')
    ->from('post')
    ->where('status', 1)
    ->is('post_title', NULL);

isNot()

public function isNot(string $column, string|null $value = null, string $logical = 'AND'): self;

Kullanım Örneği:

/** @var \InitPHP\Database\DB $db */

// SELECT `post_id` FROM `post` WHERE `status` = 1 AND `post_title` IS NOT "php"
$db->select('post_id')
    ->from('post')
    ->where('status', 1)
    ->isNot('post_title', 'php');

// SELECT `post_id` FROM `post` WHERE `status` = 1 AND `post_title` IS NOT NULL
$db->select('post_id')
    ->from('post')
    ->where('status', 1)
    ->isNot('post_title', NULL);
Clone this wiki locally