Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Why don't you try "Query Builder"

omoon
May 21, 2016

Why don't you try "Query Builder"

PHPカンファレンス福岡2016のトークで使ったスライドです
関連レポジトリ)https://github.com/omoon/phpconfuk2016

omoon

May 21, 2016
Tweet

More Decks by omoon

Other Decks in Technology

Transcript

  1. 8IZEPO`UZPVUSZ
    2VFSZ#VJMEFS
    1)1$POGFSFODF'VLVPLB
    .BZ
    4PUBSP0.63"

    View full-size slide

  2. "CPVU.F
    w4PUBSP0.63"
    w0TBLB +BQBO
    w,BOTBJ1)16TFST(SPVQ4UB⒎
    w1)1

    w-BSBWBM
    wPNPPOPSH
    w!PNPPO

    View full-size slide

  3. 2VFSZ#VJMEFS

    View full-size slide

  4. 2VFTUJPOT
    w%PZPVLOPX42-
    w.Z42-1PTUHSF42-0SBDMFPUIFST
    w1%0
    w,OPX2VFSZ#VJMEFS
    w6TF2VFSZ#VJMEFS
    w6TF03.

    View full-size slide

  5. /PUF
    w1%0
    w*MMVNJOBUF%BUBCBTF
    w.Z42-

    View full-size slide

  6. 4BNQMF%BUBCBTF

    View full-size slide

  7. 5PQJDT
    w8IBUJTl2VFSZ#VJMEFSz
    w#FOFpUT
    w)PXUPBQQMZ2VFSZ#VJMEFSUPZPVSPXOBQQ
    w4PNFFYBNQMFT

    View full-size slide

  8. 8IBU`T
    2VFSZ#VJMEFS

    View full-size slide

  9. 1JD DPOTUSVDUJPO

    View full-size slide

  10. 1JD DPOTUSVDUJPO

    View full-size slide

  11. "UPPMUPCVJME42-

    View full-size slide

  12. SELECT name, price

    FROM books

    WHERE name LIKE '%PHP%'

    ORDER BY price DESC

    LIMIT 10

    View full-size slide

  13. SELECT name, price

    FROM books

    WHERE name LIKE '%PHP%'

    ORDER BY price DESC

    LIMIT 10

    View full-size slide

  14. &BDIQBSUPG42-
    IBTB30-&

    View full-size slide

  15. $builder->table('books')

    ->where('name', 'like', '%PHP%')

    ->orderBy('name', 'desc')

    ->take(10)
    ->select('name', 'price');

    View full-size slide

  16. SELECT `name`, `price`

    FROM `books`

    WHERE `name` LIKE '%PHP%'

    ORDER BY `price` DESC

    LIMIT 10

    View full-size slide

  17. DELETE
    FROM `books`

    WHERE `name` LIKE '%PHP%'

    ORDER BY `price` DESC

    LIMIT 10

    View full-size slide

  18. $builder->table('books')

    ->where('name', 'like', '%PHP%')

    ->orderBy('price', 'desc')

    ->take(10)
    ->select('name', 'price');

    View full-size slide

  19. $builder->table('books')

    ->where('name', 'like', '%PHP%')

    ->orderBy('price', 'desc')

    ->take(10)
    ->delete();

    View full-size slide

  20. $builder->table('books')

    ->where('name', 'like', '%PHP%')

    ->orderBy('price', 'desc')

    ->take(10)
    ->select('name', 'price');

    View full-size slide

  21. $builder->table('books')

    ->where('name', 'like', '%PHP%')

    ->orderBy('price', 'desc')

    ->take(10)
    ->select('name', ‘price')
    ->get();

    View full-size slide

  22. #FOFpUT
    w&BTZUPFEJU
    w4FDVSF
    w&BTZUPSFBE
    w3FVTBCMF

    View full-size slide

  23. $name = 'PHPΤϯδχΞཆ੒ಡຊ';

    $sql = "

    SELECT `name`, `price` 

    FROM `books` 

    WHERE `name` = $name

    ORDER BY `price` DESC
    ";


    $sth = $pdo->prepare($sql);

    $sth->execute();

    View full-size slide

  24. $name = "t' OR 't' = 't";


    $sql = <<<'SQL'

    SELECT `name`, `price`

    FROM `books`

    WHERE `name` = $name

    ORDER BY `price` DESC

    SQL;


    $sth = $pdo->prepare($sql);

    $sth->execute();

    View full-size slide

  25. $name = "t' OR 't' = 't";


    $sql = <<SELECT `name`, `price`

    FROM `books`

    WHERE `name` = ?

    ORDER BY `price` DESC

    SQL;


    $sth = $pdo->prepare($sql);

    $sth->execute([$name]);

    View full-size slide

  26. $name = "t' OR 't' = 't";

    $builder->table('books')

    ->select('name', 'price')

    ->where('name', '=', $name)

    ->orderBy('name', 'desc');

    View full-size slide

  27. SELECT `name`, `price` 

    FROM `books` 

    WHERE `name` LIKE ‘%PHP%'
    AND `price` > 2000

    ORDER BY `price` DESC 

    LIMIT 10

    View full-size slide

  28. $sql = "

    SELECT `name`, `price` 

    FROM `books` 

    WHERE `name` LIKE :name

    AND `price` > :price 

    ORDER BY `price` DESC 

    LIMIT :limit 

    ";


    $sth = $pdo->prepare($sql);

    $sth->bindParam(':name', $name);

    $sth->bindParam(':price', $price);

    $sth->bindParam(':limit', $limit, PDO::PARAM_INT);

    $sth->execute();

    View full-size slide

  29. $name = '%PHP%';

    $price = 2000;

    $limit = 10;

    $sql = "SELECT `name`, `price` 

    FROM `books` 

    WHERE `name` LIKE ? AND `price` > ?

    ORDER BY `price` DESC 

    LIMIT ?";


    $sth = $pdo->prepare($sql);

    $sth->execute([$name, $price, $limit]);

    View full-size slide

  30. $name = '%PHP%';

    $price = 2000;
    $limit = 10;


    $builder->table('books')

    ->select('name', 'price')

    ->where('name', 'like', $name)

    ->where('price', '>', $price)

    ->orderBy('name', 'desc')

    ->take($limit);

    View full-size slide

  31. SELECT `name`, `price` 

    FROM `books` 

    WHERE `price` < 2000

    View full-size slide

  32. $cheap_books =

    $builder->table('books')

    ->where('price', '<', 2000);

    View full-size slide

  33. $cheap_books->orderBy('name');

    $cheap_books->orderBy('price');

    $cheap_books->orderBy('price', 'desc');

    $cheap_books->orderBy('price', ‘desc')
    ->take(10);

    View full-size slide

  34. $cheap_books->orderBy('price', ‘desc')
    ->take(10)
    ->select();

    View full-size slide

  35. $cheap_books->orderBy('price', ‘desc')
    ->take(10)
    ->delete();

    View full-size slide

  36. $cheap_books->count();

    $cheap_books->max('price');

    $cheap_books->min('price');

    $cheap_books->average('price');

    $cheap_books->sum('price');

    View full-size slide

  37. #FOFpUT
    w&BTZUPFEJU
    w4FDVSF
    w&BTZUPSFBE
    w3FVTBCMF

    View full-size slide

  38. )PXUPBQQMZ
    2VFSZ#VJMEFS
    UPZPVSPXOBQQT

    View full-size slide

  39. {

    "require": {

    "illuminate/database": "5.2.*"

    }

    }

    View full-size slide

  40. require_once '/path/to/vendor/autoload.php';

    View full-size slide

  41. use Illuminate\Database\Capsule\Manager as Builder;
    class Connection

    {

    private $host = 'localhost';

    private $username = 'user';

    private $password = 'pass';

    private $database = 'database';

    public function getBuilder()

    {

    $builder = new Builder();

    $builder->addConnection([

    'driver' => 'mysql',

    'host' => $this->host,

    'database' => $this->database,

    'username' => $this->username,

    'password' => $this->password,

    'charset' => 'utf8',

    'collation' => 'utf8_unicode_ci',

    'prefix' => '',

    ]);

    $builder->setAsGlobal();

    $builder->setFetchMode(PDO::FETCH_ASSOC);

    return $builder->connection();

    }

    }
    $builder = (new Connection())->getBuilder();

    View full-size slide

  42. 4PNFNPSFFYBNQMFT

    View full-size slide

  43. SELECT

    `books`.`name` AS `book_name`,

    `books`.`price`,

    `authors`.`name` AS `author_name`,

    `authors`.`age`

    FROM `books` LEFT JOIN `authors`

    ON `books`.`author_id` = `authors`.`id`

    WHERE `books`.`price` >= ?

    View full-size slide

  44. $results = $builder->table('books')

    ->leftJoin('authors', 'books.author_id', '=', 'authors.id')

    ->where('books.price', '>=', 5000)

    ->select(

    'books.name as book_name',

    'books.price',

    'authors.name as author_name',

    'authors.age'

    );

    View full-size slide

  45. $values = [];

    for ($i = 0; $i < 10000; $i++) {

    $values[] = [

    'name' => 'name' . $i,

    'author_id' => rand(1, 10),

    'price' => rand(500, 10000),

    ];

    }


    $sql = "INSERT INTO books (name, author_id, price)
    values (?, ?, ?)”;

    $sth = $pdo->prepare($sql);

    foreach ($values as $value) {

    $sth->execute(array_values($value));

    }


    $builder->table('books')->insert($values);

    View full-size slide

  46. INSERT INTO `books`(`name`, `author`, `price`) VALUES(?, ?, ?)

    .

    .

    .

    VS

    INSERT INTO `books`(`name`, `author`, `price`)
    VALUES(?, ?, ?),(?, ?, ?),(?, ?, ?)......

    View full-size slide

  47. NZTRMJ T
    1%0 T
    2VFSZ#VJMEFS NT

    View full-size slide

  48. UPDATE `books`

    SET `name` = CONCAT('ʲ͍҆Αʂʳ', `name`)

    WHERE `price` < 3000

    View full-size slide

  49. $cheap_books->update([

    'name' =>"CONCAT('ʲ͍҆Αʂʳ' , `name`)"

    ]);

    View full-size slide

  50. $cheap_books->update([

    'name' => $builder->raw(
    "CONCAT('ʲ͍҆Αʂʳ' , `name`)"
    )

    ]);

    View full-size slide

  51. "OZ2VFTUJPOT

    View full-size slide